SQL Server数据库级别触发器

81 阅读 0 评论 54 点赞

我是靠谱客的博主淡定牛排，最近开发中收集的这篇文章主要介绍SQL Server数据库级别触发器，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

禁止修改表结构和加表

CREATE TRIGGER [Object_Change_Trigger_DDL] ON DATABASE

FOR ALTER_TABLE,DROP_TABLE,CREATE_TABLE,CREATE_INDEX,ALTER_INDEX, DROP_INDEX  
AS
DECLARE @EventData AS XML;
SELECT @EventData = EVENTDATA();
IF @EventData.value('(/EVENT_INSTANCE/LoginName)[1]','varchar(150)') NOT IN (
'uws_M_ApricotMDM_dev'
,'Us_wangdan_temp'
,'NT AUTHORITYSYSTEM'
,'NT SERVICEMSSQLSERVER'
,'WIN-6RNHUPNK4OJAdministrator'
,'NT SERVICESQLSERVERAGENT'
,'bl_un'
) 
  BEGIN
   --RAISERROR ('创建，修改，删除表的权限已收回，如有问题请联系DBA!', 16, 1)     
   ROLLBACK
  END

GO
ENABLE TRIGGER [Object_Change_Trigger_DDL] ON DATABASE
GO

 
      
           CREATE  
           TABLE  
           [dbo].[DDLMonitor]( 
          
 
                
           [ID] [ 
           INT 
           ] IDENTITY(1,1)  
           NOT  
           NULL 
           , 
          
 
                
           [SPID] [ 
           INT 
           ]  
           NULL 
           , 
          
 
                
           [ServerName] [ 
           VARCHAR 
           ](150)  
           NULL 
           , 
          
 
                
           [PostTime] [DATETIME]  
           NULL 
           , 
          
 
                
           [EventType] [ 
           VARCHAR 
           ](300)  
           NULL 
           , 
          
 
                
           [LoginName] [ 
           VARCHAR 
           ](150)  
           NULL 
           , 
          
 
                
           [UserName] [ 
           VARCHAR 
           ](100)  
           NULL 
           , 
          
 
                
           [SchemaName] [ 
           VARCHAR 
           ](100)  
           NULL 
           , 
          
 
                
           [DatabaseName] [ 
           VARCHAR 
           ](100)  
           NULL 
           , 
          
 
                
           [ObjectName] [ 
           VARCHAR 
           ](100)  
           NULL 
           , 
          
 
                
           [ObjectType] [ 
           VARCHAR 
           ](100)  
           NULL 
           , 
          
 
                
           [TSQLCommand] [ 
           VARCHAR 
           ]( 
           MAX 
           )  
           NULL 
           , 
          
 
                
           [EventData] [XML]  
           NULL 
           , 
          
 
                
           [createdate] [DATETIME]  
           NULL  
           DEFAULT  
           (GETDATE()), 
          
 
             
           CONSTRAINT  
           [PK_DDLMonitor]  
           PRIMARY  
           KEY  
           NONCLUSTERED 
          
 
           ( 
          
 
                
           [ID]  
           ASC 
          
 
           ) 
           WITH  
           (PAD_INDEX =  
           OFF 
           , STATISTICS_NORECOMPUTE =  
           OFF 
           , IGNORE_DUP_KEY =  
           OFF 
           , ALLOW_ROW_LOCKS =  
           ON 
           , ALLOW_PAGE_LOCKS =  
           ON 
           )  
           ON  
           [ 
           PRIMARY 
           ] 
          
 
           )  
           ON  
           [ 
           PRIMARY 
           ] TEXTIMAGE_ON [ 
           PRIMARY 
           ] 
          

              
          
 
           GO 
          
 
    

--记录加的表和字段

 
           CREATE  
           TRIGGER  
           [trg_DDL_audit]  
           ON  
           DATABASE 
          
           FOR  
           ALTER_PROCEDURE,DROP_PROCEDURE,ALTER_FUNCTION,DROP_FUNCTION,ALTER_TABLE,DROP_TABLE,CREATE_TABLE 
          
           AS 
          
           DECLARE  
           @EventData  
           AS  
           XML; 
          
           SELECT  
           @EventData = EVENTDATA(); 
          
           INSERT  
           INTO  
           DDLMonitor.dbo.DDLMonitor( 
          
           SPID, 
          
           ServerName, 
          
           PostTime, 
          
           EventType, 
          
           LoginName, 
          
           UserName, 
          
           SchemaName, 
          
           DatabaseName, 
          
           ObjectName, 
          
           ObjectType, 
          
           TSQLCommand, 
          
           [EventData] 
          
           ) 
          
           VALUES 
           ( 
          
           @EventData.value( 
           '(/EVENT_INSTANCE/SPID)[1]' 
           , 
           'int' 
           ), 
          
           @EventData.value( 
           '(/EVENT_INSTANCE/ServerName)[1]' 
           , 
           'varchar(50)' 
           ), 
          
           @EventData.value( 
           '(/EVENT_INSTANCE/PostTime)[1]' 
           , 
           'datetime' 
           ), 
          
           @EventData.value( 
           '(/EVENT_INSTANCE/EventType)[1]' 
           , 
           'varchar(100)' 
           ), 
          
           @EventData.value( 
           '(/EVENT_INSTANCE/LoginName)[1]' 
           , 
           'varchar(150)' 
           ), 
          
           @EventData.value( 
           '(/EVENT_INSTANCE/UserName)[1]' 
           , 
           'varchar(100)' 
           ), 
          
           @EventData.value( 
           '(/EVENT_INSTANCE/SchemaName)[1]' 
           , 
           'varchar(100)' 
           ), 
          
           @EventData.value( 
           '(/EVENT_INSTANCE/DatabaseName)[1]' 
           , 
           'varchar(100)' 
           ), 
          
           @EventData.value( 
           '(/EVENT_INSTANCE/ObjectName)[1]' 
           , 
           'varchar(100)' 
           ), 
          
           @EventData.value( 
           '(/EVENT_INSTANCE/ObjectType)[1]' 
           , 
           'varchar(100)' 
           ), 
          
           @EventData.value( 
           '(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]' 
           , 
           'varchar(max)' 
           ), 
          
           @EventData 
          
           ) 
          
           GO 
          
           ENABLE  
           TRIGGER  
           [trg_DDL_audit]  
           ON  
           DATABASE 
          
           GO