Click here to Skip to main content
15,867,750 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 Toluie 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 Toluie 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.

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
 
Share this answer
 

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