时间:2021-07-01 10:21:17 帮助过:64人阅读
下面的Trigger用于监控存储过程的更改。
创建监控表:
- CREATE TABLE AuditStoredProcedures(
- DatabaseName sysname
- , ObjectName sysname
- , LoginName sysname
- , ChangeDate datetime
- , EventType sysname
- , EventDataXml xml
- );
创建监控Trigger:
- CREATE TRIGGER dbtAuditStoredProcedures
- ON DATABASE
- FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
- AS
- DECLARE @eventdata XML;
- SET @eventdata = EVENTDATA();
- INSERT INTOAuditStoredProcedures(DatabaseName,ObjectName,LoginName,ChangeDate,EventType,EventDataXml)
- VALUES (
- @eventdata.value('(/EVENT_INSTANCE/DatabaseName)[1]','sysname')
- , @eventdata.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')
- , @eventdata.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname')
- , GETDATE()
- , @eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname')
- , @eventdata
- );