给大家分享好东西喽！DDL触发器，追踪存储过程定义语句变更日志，及当前版本与上一版本。

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().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

SET QUOTED_IDENTIFIER OFF
GO

DISABLE TRIGGER [DDL_PROCEDURE_TRIGGER_CREATE_ALTER_DROP] ON DATABASE
GO

ENABLE TRIGGER [DDL_PROCEDURE_TRIGGER_CREATE_ALTER_DROP] ON DATABASE
GO