tsql基础-ddl触发器

DDL 触发器是一种特殊的触发器，它在响应数据定义语言 (DDL) 语句时触发。它们可以用于在数据库中执行管理任务，例如，审核以及规范数据库操作。只在sqlserver2005版本后才支持。以下使用一个案例：ddl触发器实现数据库版本控制。 

1.创建控制支撑的数据库DBController记录数据库更改历史。
      数据库核心表 t_version_details

 

2.触发器代码  trigger.sql

CREATE TRIGGER Trig_DBVersionController
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE,
    CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,
    CREATE_INDEX, ALTER_INDEX, DROP_INDEX,
    CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
    CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER,
    CREATE_VIEW, ALTER_VIEW, DROP_VIEW
AS
    SET NOCOUNT ON
    DECLARE @data AS xml
    DECLARE @clientUser AS nvarchar(128)
    DECLARE @spid AS nvarchar(128)
    DECLARE @serverName AS nvarchar(128)
    DECLARE @dbName AS nvarchar(128)
    DECLARE @dbid AS int
    DECLARE @objName AS nvarchar(512)

    SELECT @data =EVENTDATA()
    SELECT @spid = @data.value('(/EVENT_INSTANCE/SPID)[1]','nvarchar(128)')
    SELECT @serverName = @data.value('(/EVENT_INSTANCE/ServerName)[1]','nvarchar(256)')
    SELECT @dbName = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(128)')
    SELECT @objName = @data.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(128)')

    SELECT @dbid = dbid FROM sys.sysdatabases 
    WHERE name = @dbName 

    /*获取客户机的机器名*/
    SELECT @clientUser=hostname FROM master..sysprocesses WHERE spid=@spid
    -- add version record
    INSERT INTO DBController.dbo.t_version_details
    (post_computer_name, sys_dbid, uid, [schema],
    PostTime, EventType, ObjectType, ObjectName, CommandText, Remark)
    VALUES
    (@clientUser, @dbid,
    @data.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(256)'), 
    @data.value('(/EVENT_INSTANCE/UserName)[1]','nvarchar(256)'),
    @data.value('(/EVENT_INSTANCE/PostTime)[1]','datetime'),
    @data.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(128)'), 
    @data.value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(128)'),
    @objName,
    @data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'),
    ''
    )

 

3.在目标数据库上执行以上语句。

4、如果想禁用 执行语句

DISABLE TRIGGER Trig_DBVersionController ON DATABASE;