SQL Server: Can I set a trigger on ALTERing a stored proc?

Question

Everytime a stored proc is ALTERed or CREATEd, I want to capture that in a trigger to do some stuff afterwards.

Can I do that in SQL Server 2008?

Thank you. --Beemer

Answer 1

This one will probably work in 2k8 as well

Answer 2

If you want to do somethin withing the ALTER/CREATE/DROP context (ie. inside the same transaction) and even prevent the ALTER/CREATE/DROP then you should look into DDL Triggers.

If you want to do something after the ALTER/CREATE/DROP , in a separate transaction, and have the liberty to do some lengthy processing without slowing down the original DDL, then you should look into Event Notifications.

Note that Event Notifications can actually be delivered remotely so you can capture all DDL events from an entire corporate group into one single central repository.

Answer 3

CREATE TRIGGER tddl_storedprocevents ON DATABASE
FOR
CREATE_PROCEDURE,ALTER_PROCEDURE,DROP_PROCEDURE
AS
INSERT INTO AUDIT_TABLE(EventType, SQLCommand, etc)
  SELECT EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(512)')
       , EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)')
             , etc

/*
EVENTDATA()

SELECT EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(512)')
     , EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')
     , EVENTDATA().value('(/EVENT_INSTANCE/SPID)[1]', 'nvarchar(4)')
     , EVENTDATA().value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(512)')
     , EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(512)')
     , EVENTDATA().value('(/EVENT_INSTANCE/LoginType)[1]', 'nvarchar(512)')
     , EVENTDATA().value('(/EVENT_INSTANCE/SID)[1]', 'nvarchar(512)')
     , EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'nvarchar(512)')
     , EVENTDATA().value('(/EVENT_INSTANCE/IsPooled)[1]', 'nvarchar(1)')
etc
*/

DROP TRIGGER tddl_storedprocevents ON DATABASE

I recommend reading the documentation about the xml object returned by the EVENTDATA(), if you didn't do it by now.

Hope it helps.