DDL触发器追踪存储过程定义语句(二)
.value('(/EVENT_INSTANCE/SchemaName)[1]',
'nvarchar(max)') ,
@ObjectName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]',
'nvarchar(max)') ,
@ObjectType = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]',
'nvarchar(max)') ,
@CommandText = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
'nvarchar(max)')
----写入执行日志表
INSERT INTO dbo.ddl_proc_log
( EventType ,
PostTime ,
SPID ,
ServerName ,
LoginName ,
DatabaseName ,
SchemaName ,
ObjectName ,
ObjectType ,
CommandText
)
SELECT @EventType ,
@PostTime ,
@SPID ,
@ServerName ,
@LoginName ,
@DatabaseName ,
@SchemaName ,
@ObjectName ,
@ObjectType ,
@CommandText
----当脚本发生变化时，进行写入；并且只保留最近两次不同脚本；
IF ( @EventType IN ( 'CREATE_PROCEDURE', 'ALTER_PROCEDURE' )
AND NOT EXISTS ( SELECT 1
FROM dbo.ddl_proc_last_current a
WHERE a.ObjectName = @ObjectName
AND a.CommandText = @CommandText )
)
BEGIN
----写入最新不同脚本
INSERT INTO wfp.dbo.ddl_proc_last_current
( EventType ,
PostTime ,
SPID ,
ServerName ,
LoginName ,
DatabaseName ,
SchemaName ,
ObjectName ,
ObjectType ,
CommandText
)
SELECT @EventType ,
@PostTime ,
@SPID ,
@ServerName ,
@LoginName ,
@DatabaseName ,
@SchemaName ,
@ObjectName ,
@ObjectType ,
@CommandText ;
----删除历史最旧版本，保留最近两次不同的最新版本
WITH cte1
AS ( SELECT ROW_NUMBER() OVER ( PARTITION BY ObjectName ORDER BY rowid DESC ) xh ,
*
FROM dbo.ddl_proc_last_current
WHERE ObjectName = @ObjectName
)
DELETE FROM cte1
WHERE xh > 2 ;
----对两次最新版本信息进行标记
WITH cte2
AS ( SELECT ROW_NUMBER() OVER ( PARTITION BY ObjectName ORDER BY rowid DESC ) xh ,
*
FROM dbo.ddl_proc_last_current
WHERE ObjectName = @ObjectName
)
UPDATE cte2
SET version = CASE WHEN xh = 1 THEN 'current'
ELSE 'last'
END
END
END
SET NOCOUNT OFF
GO
SET ANSI_NULLS OFF
GO