Trigger event for stored procedures...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Trigger event for stored procedures...

Author

Topic

Cowski
Starting Member

30 Posts

Posted - 2010-04-27 : 12:22:43

Is there a trigger event out there that would trip if a stored procedure is altered/deleted? If so, what is it? I'm at my wits end with this search.

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-27 : 12:25:33

you can use DDL triggers for that
http://msdn.microsoft.com/en-us/library/ms190989.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Cowski
Starting Member

30 Posts

Posted - 2010-04-27 : 14:19:43

quote:
Originally posted by visakh16

you can use DDL triggers for that
http://msdn.microsoft.com/en-us/library/ms190989.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I checked out the DDL aspect of it & found this note regarding DDL triggers:

"DDL triggers do not fire in response to events that affect local or global temporary tables and stored procedures."

Am I reading that right that DDL triggers don't do what I'm looking for?
I found this as the 2nd note on this page: http://technet.microsoft.com/en-us/library/ms186406.aspx

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-27 : 14:37:04

I tried for Create SP and Alter SP and its raising trigger.

Refer the below link:
http://technet.microsoft.com/en-us/library/bb522542.aspx

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-27 : 14:38:50

This is the code i tried:

Create TRIGGER ProcAlterCheck1

ON DATABASE
FOR create_PROCEDURE,Alter_procedure
AS
RAISERROR ('Checking the trigger',16,1)
ROLLBACK
go

create procedure procTest1
as
Select * from DataTable

Please correct if my understanding is wrong or your requirement is something different.

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-28 : 05:42:55

it will get fired for create/alter proc , at least in 2008

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-28 : 11:01:35

I have 2005 and its firing..
So DDL trigger for Create / Alter sp is supported in 2005 also

I am here to learn from Masters and help new bees in learning.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-28 : 11:06:49

ok...i didnt had sql 2005 box to test then. thanks for confirming

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources