DDL触发器追踪存储过程定义语句
[sql]
USE [MyTargetDB]
GO
/****** Object: DdlTrigger [DDL_PROCEDURE_TRIGGER_CREATE_ALTER_DROP] Script Date: 04/09/2013 09:47:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [DDL_PROCEDURE_TRIGGER_CREATE_ALTER_DROP] ON DATABASE
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
AS
/*
作者：陈恩辉
本触发器只对 CREATE_PROCEDURE, ALTER_PROCEDURE,DROP_PROCEDURE 进行跟踪
历史日志写入 ddl_proc_log
最新两次不同脚本写入 ddl_proc_last_current
*/
SET NOCOUNT ON
BEGIN
IF OBJECT_ID('[dbo].[ddl_proc_last_current]', 'U') IS NULL
CREATE TABLE [dbo].[ddl_proc_last_current]
(
[rowid] [int] IDENTITY(1, 1) NOT NULL ,
[EventType] [varchar](20) NULL ,
[PostTime] [datetime] NULL ,
[SPID] [int] NULL ,
[ServerName] [varchar](255) NULL ,
[LoginName] [varchar](255) NULL ,
[DatabaseName] [varchar](255) NULL ,
[SchemaName] [varchar](20) NULL ,
[ObjectName] [nvarchar](128) NULL ,
[ObjectType] [nvarchar](20) NULL ,
[CommandText] [varchar](MAX) NULL ,
[version] [varchar](20) NULL
)
ON [PRIMARY]
IF OBJECT_ID('[dbo].[ddl_proc_log]', 'U') IS NULL
CREATE TABLE [dbo].[ddl_proc_log]
(
[rowid] [int] IDENTITY(1, 1) NOT NULL ,
[EventType] [varchar](20) NULL ,
[PostTime] [datetime] NULL ,
[SPID] [int] NULL ,
[ServerName] [varchar](255) NULL ,
[LoginName] [varchar](255) NULL ,
[DatabaseName] [varchar](255) NULL ,
[SchemaName] [varchar](20) NULL ,
[ObjectName] [nvarchar](128) NULL ,
[ObjectType] [nvarchar](20) NULL ,
[CommandText] [nvarchar](MAX) NULL ,
[remark] [varchar](50) NULL ,
[commandtext_check] AS ( CHECKSUM([commandtext]) ) PERSISTED
)
ON [PRIMARY]
DECLARE @EventType VARCHAR(20) ,
@PostTime DATETIME ,
@SPID INT ,
@ServerName VARCHAR(255) ,
@LoginName VARCHAR(255) ,
@DatabaseName VARCHAR(255) ,
@SchemaName VARCHAR(20) ,
@ObjectName NVARCHAR(128) ,
@ObjectType NVARCHAR(20) ,
@CommandText NVARCHAR(MAX)
----解析事件字段内容
SELECT @EventType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]',
'nvarchar(max)') ,
@PostTime = EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]',
'datetime') ,
@SPID = EVENTDATA().value('(/EVENT_INSTANCE/SPID)[1]', 'int') ,
@ServerName = EVENTDATA().value('(/EVENT_INSTANCE/ServerName)[1]',
'nvarchar(max)') ,
@LoginName = EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]',
'nvarchar(max)') ,
@DatabaseName = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]',
'nvarchar(max)') ,
@SchemaName = EVENTDATA()