SQL Server：我可以在ALTERing存储过程中设置触发器吗？

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

每次存储过程都是ALTER或CREATEd时，我想在触发器中捕获它以后再做一些事情。

Can I do that in SQL Server 2008?

我可以在SQL Server 2008中执行此操作吗？

Thank you. --Beemer

谢谢。 --Beemer

2 个解决方案

#1

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.

如果你想使用ALTER / CREATE / DROP上下文（即在同一个事务中）做某些事情，甚至阻止ALTER / CREATE / DROP，那么你应该研究DDL触发器。

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.

如果你想在ALTER / CREATE / DROP之后做一些事情，在一个单独的事务中，并且可以*地做一些冗长的处理而不减慢原始的DDL，那么你应该查看事件通知。

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.

请注意，事件通知实际上可以远程传递，因此您可以将整个公司组中的所有DDL事件捕获到一个*存储库中。

#2

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.

我建议您阅读有关EVENTDATA（）返回的xml对象的文档，如果您现在没有这样做的话。

Hope it helps.

希望能帮助到你。

#1