当前位置:Gxlcms > mssql > SQLServer中使用Trigger监控存储过程更改脚本实例

SQLServer中使用Trigger监控存储过程更改脚本实例

时间:2021-07-01 10:21:17 帮助过:64人阅读

下面的Trigger用于监控存储过程的更改。
 
创建监控表:
 

  1. CREATE TABLE AuditStoredProcedures(
  2. DatabaseName sysname
  3. , ObjectName sysname
  4. , LoginName sysname
  5. , ChangeDate datetime
  6. , EventType sysname
  7. , EventDataXml xml
  8. );

创建监控Trigger:

  1. CREATE TRIGGER dbtAuditStoredProcedures
  2. ON DATABASE
  3. FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
  4. AS
  5. DECLARE @eventdata XML;
  6. SET @eventdata = EVENTDATA();
  7. INSERT INTOAuditStoredProcedures(DatabaseName,ObjectName,LoginName,ChangeDate,EventType,EventDataXml)
  8. VALUES (
  9. @eventdata.value('(/EVENT_INSTANCE/DatabaseName)[1]','sysname')
  10. , @eventdata.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')
  11. , @eventdata.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname')
  12. , GETDATE()
  13. , @eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname')
  14. , @eventdata
  15. );

人气教程排行