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

--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