Click here to Skip to main content
14,920,935 members
Please Sign up or sign in to vote.
2.50/5 (2 votes)
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.
SQL
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
Updated 22-Feb-13 7:07am
v3
Comments
Sergey Alexandrovich Kryukov 22-Feb-13 13:39pm
   
And where is your question?
—SA
Meysam Tolouee 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 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
ZurdoDev 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 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.
ZurdoDev 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 22-Feb-13 14:04pm
   
Agree. See also my comments...
—SA
Meysam Tolouee 22-Feb-13 23:55pm
   
See my reply to Sergey.
Sergey Alexandrovich Kryukov 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 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

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
   

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900