SQL SERVER 觸發器

標籤：

觸發器是一種特殊的預存程序，觸發器主要是通過事件進行觸發而被自動調用執行，而預存程序必須通過預存程序的名稱被調用。

一、觸發器的定義

觸發器是在對錶進行插入、更新或刪除操作時自動執行的特殊預存程序。觸發器通常用於強制商務規則，觸發器是一種進階約束，可以定義比CHECK約束更為複雜的約束：可以執行複雜的SQL語句（if/while/case）,可以引用其他表中的列。觸發器定義在特定的表上，與表相關，自動觸發執行，不能直接調用，是一個事務（可復原）。

二、觸發器分類

SQL SERVER中觸發器可以分為兩類：DML觸發器和DDL觸發器，DML觸發器針對錶，DDL觸發器會影響多種資料定義語言 (Data Definition Language)語句而觸發，這些語句有create、alter、drop語句。

DML觸發器分為：

1、after觸發器（之後觸發）

     a、insert觸發器

     b、update觸發器

     c、delete觸發器

2、instead of觸發器（之前觸發）

     after觸發器要求只有執行某一操作（insert、update、delete）之後觸發器才能被觸發，且只能定義在表上。而instead of觸發器表示並不執行其定義的操作（insert、update、delete）而僅是執行觸發器本身，其優先順序高於觸發語句的執行。

    觸發器有兩個特殊的表：插入表（instered表）和刪除表(deleted表)

    這兩張表是邏輯表也是虛表，觸發器觸發時系統自動在記憶體中建立這兩張表，不會儲存在資料庫中。這兩張表都是唯讀，不允許修改。這兩張表的結果總是與被觸發器應用的表的結構相同。當觸發器完成工作後，這兩張表就會被刪除。inserted表臨時儲存了插入或更新後的記錄行，可以從inserted表中檢查插入的資料是否滿足業務需求，如果不滿足，則向使用者報告錯誤訊息，並復原插入操作。deleted表臨時儲存了刪除或更新前的記錄行，可以從deleted表中檢查被刪除的資料是否滿足業務需求，如果不滿足，則向使用者報告錯誤訊息，並復原刪除操作。update資料的時候是先刪除表記錄，然後插入一條記錄，在inserted和deleted表就都有update後的資料記錄了。

     inserted表和deleted表存放的資訊：

修改操作	inserted表	deleted表
增加(INSERT)記錄	存放新增的記錄	無
刪除(DELETE)記錄	無	存放被刪除的記錄
修改(UPDATE)記錄	存放更新後的記錄	存放更新前的記錄

三、建立觸發器

      文法：

     CREATE TRIGGER trigger_name

     ON table_name

     [WITH ENCRYPTION]

     FOR [DELETE, INSERT, UPDATE]

     AS

       T-SQL語句

     GO

    WITH ENCRYPTION表示加密觸發器定義的SQL文本

    DELETE, INSERT, UPDATE指定觸發器的類型

1、建立insert類型的觸發器

--插入觸發器
--GradeInfo表中插入一條資料，MyStudentInfo表中插入一條記錄
IF (object_id(‘tr_insert‘,‘tr‘) is not null)
    drop trigger tr_insert
GO
CREATE trigger tr_insert
on GradeInfo
after insert --插入觸發
as
 begin
   --定義變數
   declare @GradeId int
   --在inserted表中查詢已經插入記錄資訊
   select @GradeId=id from INSERTED
   --MyStudentInfo表中插入資料
   insert INTO MyStudentInfo (GradeId) VALUES (@GradeId)
   print ‘插入成功!‘
 end

 --插入資料
 insert INTO GradeInfo VALUES (11,‘C++‘)

 --查詢資料
 select * from MyStudentInfo where GradeId=11

 

2、delete觸發器
 --刪除MyStudentInfo表中的資料，插入備份表
 IF (object_id(‘tr_Delete‘,‘tr‘) is not null)
    drop TRIGGER tr_Delete
 GO
 CREATE trigger tr_Delete
 on MyStudentInfo
 for delete
 as
  begin
    print ‘正在備份資料......‘
 IF (object_id(‘MyStudentInfo_Back‘,‘U‘) is not null)
 --存在表，直接插入資料
   insert INTO MyStudentInfo_Back SELECT * from DELETED
 else
   select * into MyStudentInfo_Back from DELETED
 PRINT ‘備份完成‘
  end

  --刪除前查詢MyStudentInfo表資料
  select * from MyStudentInfo

 

   --刪除id=9的資料
  delete FROM MyStudentInfo where Id=9

  --查詢備份表資料
  select * from MyStudentInfo_Back

3、update觸發器
  IF (object_id(‘tr_Update‘,‘tr‘) is not null)
     drop TRIGGER tr_Update
  GO
  CREATE trigger tr_Update
  on MyStudentInfo
  for update
  as
    begin
   --聲明變數，儲存更新前和更新後的姓名
   declare @OldName varchar(16),@NewName varchar(16)
   select @OldName=name from DELETED
   print ‘更新前姓名:‘[email protected]
   select @NewName=name from INSERTED
   print ‘更新后姓名:‘[email protected]
 end

--把張三更新為"張三測試"
 update MyStudentInfo SET Name=‘張三測試‘ where Id=1

 --update更新列級觸發器
   IF (object_id(‘tr_update_column‘,‘tr‘) is not null)
      drop TRIGGER tr_update_column
   GO
   CREATE trigger tr_update_column
   on GradeInfo
   for update
   as
     begin
    IF(update(id))
      begin
     print ‘系統提示：主鍵ID不能更新‘
     rollback
   end
  end

 

 --更新id列
update GradeInfo SET Id=15 where Id=4

 

4、instead of觸發器

     instead of觸發器表示並不執行其定義的操作（insert、update、delete）而僅是執行觸發器本身的內容，其優先順序高於定義的SQL語句的執行

文法：

create trigger tgr_name
on table_name
with encryption
    instead of update...
as
    begin
  T-SQL
 end

--建立instead of觸發器
/*MyStudentInfo表裡面插入資料之前，先判斷GradeInfo表中是否有對應的班級ID，如果沒有，不允許插入，如果存在，則插入 */
IF (object_id(‘tr_insteadOf‘,‘tr‘) is not null)
   drop TRIGGER tr_insteadOf
GO
CREATE trigger tr_insteadOf
on MyStudentInfo
instead of insert
as
  begin
     IF exists(SELECT * FROM GradeInfo WHERE Id=(SELECT GradeId FROM INSERTED))
      print ‘該班級存在，可以插入‘
  else
    begin
      print ‘該班級不存在，不可以插入‘
   rollback
    end
  end

  --測試1，插入不存在的班級id
  insert INTO MyStudentInfo (GradeId) VALUES (15)

 

  --測試2，插入存在的班級id
  insert INTO MyStudentInfo (GradeId) VALUES (5)

DDL觸發器

  create trigger tr_DDL on database
  for DROP_TABLE,ALTER_TABLE
  as
    begin
      print ‘別想著刪庫！好好打你的代碼‘
   rollback --復原
 end

--測試刪除表
drop TABLE MyStudentInfo

 

--測試修改表結構
alter table MyStudentInfo
alter column Name varchar(32)

 

 

 --禁用DML觸發器
 disable trigger tr_insteadOf on MyStudentInfo

 --啟用DML觸發器
 enable trigger tr_insteadOf on MyStudentInfo

 --禁用DDL觸發器
 disable trigger tr_DDL on database

 --啟用DDL觸發器
 enable trigger tr_DDL on database

SQL SERVER 觸發器