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

时间：2020-04-15 13:41 来源/作者：MSSQL教程网

下面的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

									);

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

相关文章

热门资讯