SQL Server資料庫級別觸發器

zping發表於2016-04-15
SQLServer資料庫觸發器

  禁止修改表結構和加表

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 AUTHORITY\SYSTEM'
,'NT SERVICE\MSSQLSERVER'
,'WIN-6RNHUPNK4OJ\Administrator'
,'NT SERVICE\SQLSERVERAGENT'
,'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

 

 --禁用當前資料庫中所有資料庫級別的 DDL 觸發器：
  DISABLE TRIGGER ALL ON DATABASE
  --禁用伺服器例項中所有伺服器級別的 DDL 觸發器：
  DISABLE TRIGGER ALL ON ALL SERVER

  

  SELECT * FROM sys.server_triggers

  ENABLE Trigger ALL ON ALL SERVER;

相關文章