SQL Server基礎之庫級觸發器

  觸發器分為兩種，一種與數據表綁定，響應數據表指定動作（insert、delete或update），此處稱為表級；一種與資料庫本身綁定，響應數據定義(DDL)語句（主要是CREATE、ALTER 和 DROP 開頭的語句），此處稱為庫級。本篇以下所說觸發器皆指庫級觸發器。

  觸發器(又名DDL觸發器)是一段有特定語法，實現一定功能的SQL語句，它與數據庫本身綁定，當數據庫發生指定動作（主要是CREATE、ALTER 和 DROP 開頭的語句，完整響應事件請看這裡）時，由資料庫自動調用。觸發器可以選擇綁定某一個特定的資料庫，也可以選擇綁定所有的資料庫。當綁定特定資料庫時，觸發器位於圖1紅框位置，當綁定所有資料庫時，位於圖2紅框位置。無論是綁定特定資料庫還是綁定所有資料庫的觸發器，均可以同時存在多個，並且響應同一事件的觸發器也可以同時存在多個。當存在多個響應同一事件的觸發器時，按觸發器建立時間先後決定執行順序，先建立的先執行。觸發器是不能修改的，如果需要修改，必須先刪除再新建。值得特別註意的是，綁定所有資料庫時觸發器可以響應伺服器級別的事件，如CREATE_DATABASE，而綁定特定資料庫時，只能響應與該資料庫相關的事件。

   觸發器主要用於防止或者記錄對資料庫架構的更改。下麵是一個防止非sa用戶創建表、綁定所有資料庫的觸發器。

USE [master]
GO
create trigger [tr_FanZhiFeiSaChuangJianBiao]
on all server --作用於SQL Server實例下所有庫
for CREATE_TABLE
as      
  --獲取事件數據
  DECLARE @data XML
  SET @data = EVENTDATA()

  --獲取用戶名
  declare @LoginName nvarchar(150)  
  set @LoginName = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname')

  --如果用戶名不是sa則回滾
  if @LoginName<>'sa'
    begin 
        rollback
        return
    end 

觸發器的語法：

CREATE TRIGGER trigger_name --觸發器的名字
ON {database | all server} --確定觸發器是綁定特定數據還是所有資料庫,database代表綁定特定資料庫
{FOR | CREATE_TABLE|CREATE_PROCEDURE }  --確定觸發器響應事件，可以同時綁定多個，多個時使用‘，’分隔，綁定所有事件使用DDL_SERVER_LEVEL_EVENTS
 AS           
sql_statement  --你需要編寫的SQL語句

--刪除綁定所有資料庫的觸發器
drop trigger trigger_name on all server
--刪除綁定特定資料庫的觸發器
drop trigger trigger_name on database

-- 啟動綁定特定資料庫的觸發器
enable trigger trigger_name on database;
-- 禁用名綁定特定資料庫的觸發器
disable trigger trigger_name on database;

-- 啟動綁定所有資料庫的觸發器
enable trigger trigger_name on all server;
-- 禁用名綁定所有資料庫的觸發器
disable trigger trigger_name on all server;

--查詢綁定特定資料庫的觸發器
select * from sys.triggers

--查詢綁定所有資料庫的觸發器
select * from sys.server_triggers

/*觸發器不存在於sys.objects 目錄視圖中，無法對其使用OBJECT_ID 函數*/

  在觸發器里使用 EVENTDATA()函數，可以獲得觸發器上下文事件信息。其返回的是一個XML文件，典型內容如下圖。

--讀取 EVENTDATA()返回的XML
DECLARE @data XML  
SET @data = EVENTDATA()

--讀取XML節點
DECLARE @dbName nvarchar(250) 
SET @dbName = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'sysname')

 寫作時間：2018-10-05

=====================================================================================
本文只代表本人的見解，可能存在錯誤，僅用於技術交流。如果你喜歡該文，可以掃下麵的二維碼打賞我（打賞敬請備註“博客園打賞”五字）。