SQL Server DDL的Trigger觸發程序

之前有介紹過DML的觸發程序 - 這裡
這裡要說明的是DDL的觸發程序
在資料庫中有任何的CREATE、DROP、ALTER都算

----------------------------------------------------------------------
CREATE TRIGGER 本伺服器資料庫禁止異動
ON ALL SERVER
AFTER ALTER_DATABASE,DROP_DATABASE
AS
 RAISERROR('禁止異動伺服器資料庫!!',16,10)
 ROLLBACK TRANSACTION
GO

USE 新新
GO

CREATE TRIGGER 本資料庫禁止異動
ON DATABASE
AFTER ALTER_TABLE,DROP_TABLE
AS
 RAISERROR('禁止異動任何TABLE!',16,10)
 ROLLBACK TRANSACTION
GO
--刪除Trigger---------------------------------------------------------
DROP TRIGGER 本資料庫禁止異動 ON DATABASE
DROP TRIGGER 本伺服器資料庫禁止異動 ON ALL SERVER

再來除了禁止，有些使用者權限比較大
但為了避免紛爭，可以利用EVENTDATA()函數
實現每次有異動時，都紀錄一筆資料
並附上SQL Server說明檔的異動事件參考指令層級圖

/*
--EVENTDATA()xml表的結構
--<event_instance>
--    <eventtype>type</eventtype>
--    <posttime>date-time</posttime>
--    <spid>spid</spid>
--    <servername>name</servername>
--   <loginname>name</loginname>
--    <username>name</username>
--    <databasename>name</databasename>
--    <schemaname>name</schemaname>
--    <objectname>name</objectname>
--    <objecttype>type</objecttype>
--    <tsqlcommand>command</tsqlcommand>
--</event_instance>


CREATE TABLE DDL追蹤表
(
    登入帳號 VARCHAR(200),
 使用者帳號 VARCHAR(200),
 物件種類 VARCHAR(50),
 結構名 VARCHAR(50),
 物件名 VARCHAR(200),
 TSQL命令 VARCHAR(MAX),
 發生時間 DATETIME DEFAULT GETDATE()
)
GO

CREATE TRIGGER 資料庫DDL追蹤
ON DATABASE
AFTER DDL_DATABASE_LEVEL_EVENTS --所有的DDL資料庫事件
AS
  DECLARE @ee XML
  SET @ee=EVENTDATA()
  INSERT INTO DDL追蹤表(登入帳號,使用者帳號,物件種類,結構名,物件名,TSQL命令)
  VALUES(
 @ee.value('(EVENT_INSTANCE/LoginName)[1]','VARCHAR(200)'),
 @ee.value('(EVENT_INSTANCE/UserName)[1]','VARCHAR(200)'),
 @ee.value('(EVENT_INSTANCE/ObjectType)[1]','VARCHAR(50)'),
 @ee.value('(EVENT_INSTANCE/SchemaName)[1]','VARCHAR(50)'),
 @ee.value('(EVENT_INSTANCE/ObjectName)[1]','VARCHAR(200)'),
 @ee.value('(EVENT_INSTANCE/TSQLCommand)[1]','VARCHAR(MAX)')
  )
GO