sql数据库级触发器的应用

sql数据库级触发器的应用

适用范围：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 TRIGGER STOP_DDL_on_Table_and_PROC   
    ON DATABASE  
     FOR CREATE_TABLE,DROP_TABLE,ALTER_TABLE,CREATE_PROCEDURE,ALTER_PROCEDURE,DROP_PROCEDURE   
     AS  
    
     SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') 
     PRINT 'You are not allowed to CREATE,ALTER and DROP    any Tables and Procedures'  
     ROLLBACK;

 

3.禁止在服务器上增删改数据库
    CREATE TRIGGER STOP_DDL_on_Table_and_PROC 
  ON ALL SERVER    FOR CREATE_DATABASE,ALTER_DATABASE,DROP_DATABASE   
AS   
PRINT 'You are not allowed to CREATE,ALTER and DROP any Databases'  
 ROLLBACK;

 

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/manimanihome/archive/2009/05/15/4188416.aspx