SQL2005以上版本数据库级触发器的应用

原帖：http://blog.youkuaiyun.com/htl258/archive/2010/03/29/5430315.aspx

 

--SQL2005 以上版本数据库级触发器的应用

 

 

--1. 记录数据库结构的所有变化

CREATE TABLE [dbo] . [tblAuditLog] (

  [ID] [int] IDENTITY ( 1, 1) NOT NULL,

  [Command] [nvarchar] ( 1000)   NULL,

  [PostTime] [nvarchar] ( 24)   NULL,

  [HostName] [nvarchar] ( 100)   NULL,

  [LoginName] [nvarchar] ( 50)   NULL,

  [CreateTime] [datetime] NOT NULL CONSTRAINT [DF_AuditLog_CreateTime]   DEFAULT ( getdate ())

) ON [PRIMARY]

GO

CREATE TRIGGER Audit ON DATABASE

FOR DDL_DATABASE_LEVEL_EVENTS

AS

    DECLARE   @data xml

    DECLARE   @cmd NVARCHAR ( 1000)

    DECLARE   @posttime NVARCHAR ( 24)

    DECLARE   @spid NVARCHAR ( 6)

    DECLARE   @loginname NVARCHAR ( 100)

    DECLARE   @hostname NVARCHAR ( 100)

    SET @data = EVENTDATA ()

    SET @cmd = @data . value ( '(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]' , 'NVARCHAR(1000)' )

    SET @cmd = LTRIM ( RTRIM ( REPLACE ( @cmd , '' , '' )))

    SET @posttime = @data . value ( '(/EVENT_INSTANCE/PostTime)[1]' , 'NVARCHAR(24)' )

    SET @spid = @data . value ( '(/EVENT_INSTANCE/SPID)[1]' , 'nvarchar(6)' )

    SET @loginname = @data . value ( '(/EVENT_INSTANCE/LoginName)[1]' , 'NVARCHAR(100)' )

    SET @hostname = HOST_NAME ()

    INSERT INTO dbo . tblAuditLog ( Command , PostTime , HostName , LoginName )

    VALUES ( @cmd , @posttime , @hostname , @loginname )

GO

 

--2. 禁止在数据库上增删改表结构、存储过程、触发器与函数

CREATE DROP TRIGGER STOP_DDL_on_Table_and_PROC    

ON DATABASE   

FOR CREATE_TABLE , DROP_TABLE , ALTER_TABLE ,

    CREATE_PROCEDURE , ALTER_PROCEDURE , DROP_PROCEDURE ,

    CREATE_TRIGGER , ALTER_TRIGGER , DROP_TRIGGER ,

    CREATE_FUNCTION , ALTER_FUNCTION , DROP_FUNCTION    

AS  

     SELECT EVENTDATA (). value ( '(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]' , 'nvarchar(max)' )  

     PRINT ' 不允许创建、修改、删除表、存储过程、触发器和函数！ '   

     ROLLBACK ;

GO

--3. 禁止在服务器上增删改数据库

CREATE TRIGGER STOP_DDL_on_Table_and_PROC ON ALL SERVER

FOR CREATE_DATABASE , ALTER_DATABASE , DROP_DATABASE    

AS    

    PRINT ' 不允许创建、修改、删除数据库 '   

    ROLLBACK ;

GO

--4. 禁用、启用、删除库级触发器

-- 禁用触发器

DISABLE TRIGGER  库级触发器名 ON DATABASE

-- 启用触发器

ENABLE TRIGGER 库级触发器名 ON DATABASE

-- 删除触发器

DROP TRIGGER 库级触发器名 ON DATABASE