Click here to Skip to main content
13,193,970 members (48,702 online)
Rate this:
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
	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'
                -- 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 6:52am
Updated 22-Feb-13 7:07am
Sergey Alexandrovich Kryukov 22-Feb-13 13:39pm
And where is your question?
ryanb31 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: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.
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.
ryanb31 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: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.
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.
Sergey Alexandrovich Kryukov 22-Feb-13 14:04pm
Agree. See also my comments...
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.
Meysam Tolouee 22-Feb-13 23:55pm
See my reply to Sergey.

1 solution

Rate this: bad
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[^]


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

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web03 | 2.8.171018.2 | Last Updated 23 Feb 2013
Copyright © CodeProject, 1999-2017
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