Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
Hello Friends
I need to create a trigger on Sys.Procedures.
I know the syntax of creating trigger but I don't know how to access the inserted row fields.
I mean, my trigger statement depends on the fields of the inserted row in the Sys.Procedures.
Create	Trigger	trigger_name
On	Sys.Procedures
After	Insert
As
	Declare	@action		Varchar(6)
	Set	@action =
		Case	Sys.Procedures.Name 
			When	N'Get_%'	Then	N'Get'
			When	N'Insert_%'	Then	N'Insert'
			When	N'Delete_%'	Then	N'Delete'
			When	N'Update_%'	Then	N'Update'
		End
                -- the statement continues
I wrote the above code, because I think 'Sys.Procedures.Name' has the last inserted row in the 'Sys.Procedures' table. Am I right?
The following error will raise when I try to execute the above query.
The object 'Sys.Procedures' does not exist or is invalid for this operation.
Posted 22-Feb-13 7:52am
Edited 22-Feb-13 8:07am
v3
Comments
Sergey Alexandrovich Kryukov at 22-Feb-13 13:39pm
   
And where is your question?
—SA
Meysam Tolouee at 22-Feb-13 13:47pm
   
First of all you could answer "Am I right?", didn't you?
After that you can see my problem, so if you can suggest something to solve it, please help me; otherwise do not look for a question.
Sergey Alexandrovich Kryukov at 22-Feb-13 14:01pm
   
Sorry that I did not spot it. You are right. Please don't look at it as if I attacked you or something, and don't be rude yourself. Let me explain:
 
You see, these days, we are overwhelmed with a lot of non-questions. Even though you are right about your particular question, you are the one who is interested the most that you show a distinct question in your post. If you don't, it's too likely that your question would be accidentally removed. Now imaging what would you feel if you browse through the posts which are supposed to be questions but actually are not, and most posts are like that. It's easy to miss a real question.
 
So, I apologize, but please take some patience and don't get mad about it.
 
Thank you for understanding.
—SA
ryanb31 at 22-Feb-13 13:42pm
   
I see what you said you are trying to do, but what exactly are you trying to do? What is the purpose?
Meysam Tolouee at 22-Feb-13 13:50pm
   
As you can see I commented that the statement continues.'.
The purpose is not important yet. For now my problem is to create the trigger.
ryanb31 at 22-Feb-13 13:52pm
   
The purpose is important. Microsoft says "don't touch" those sys.etc system views so I don't know if what you are trying is even supported.
Sergey Alexandrovich Kryukov at 22-Feb-13 14:04pm
   
Agree. See also my comments...
—SA
Meysam Tolouee at 22-Feb-13 23:55pm
   
See my reply to Sergey.
Sergey Alexandrovich Kryukov at 22-Feb-13 14:03pm
   
And the purpose is always important even if you don't think so. You may be sure that you are doing and want right thing, but we cannot assume that. Many inquirers want wrong things, and, if they not explain the goals, they loose chance to get an advice.
—SA
Meysam Tolouee at 22-Feb-13 23:53pm
   
I am creating many procedures in a Db one by one. For managing what procedure is being created or not I had made a DB with a single Table; Now I want to automatically update the progress table when creating the target Db procedures.
At first I thought the sys.etc kind of metadata are tables which can support triggers; Now I am looking for more sufficient ideas.

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hi Friend,
 
You need to make Audit of all New Procedures and Track the Procedure Changes with Procedure Text right.
 
Here clearly explained how to do Audit in care of any procedure changes in DB. You will go for
Database Trigger and get the details from Trigger and Store in a table in Different DB also.
 
Check the following link....
 
Try this, If any difficulty let me know....
 
SQL Server DDL Triggers to Track All Database Changes[^]
 

Regards,
GVPrabu
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 431
1 Afzaal Ahmad Zeeshan 184
2 /\jmot 176
3 Sergey Alexandrovich Kryukov 175
4 Marcin Kozub 175
0 OriginalGriff 8,344
1 Sergey Alexandrovich Kryukov 7,407
2 DamithSL 5,634
3 Maciej Los 5,024
4 Manas Bhardwaj 4,986


Advertise | Privacy | Mobile
Web02 | 2.8.1411023.1 | Last Updated 23 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100