SQL SERVER觸發器詳解

2022-02-24 16:00:10

一、觸發器的定義

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

二、觸發器分類

SQL SERVER中觸發器可以分為兩類：DML觸發器和DDL觸發器，DML觸發器針對表，DDL觸發器會影響多種資料定義語言語句而觸發，這些語句有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表中的資料，插入備份表

--刪除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 '更新前姓名:'+@OldName
   select @NewName=name from INSERTED
   print '更新後姓名:'+@NewName
 end

把張三更新為"張三測試"

update MyStudentInfo SET Name='張三測試' where Id=1

update更新列級觸發器

 --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觸發器

--建立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觸發器詳解的文章就介紹到這了。希望對大家的學習有所幫助，也希望大家多多支援it145.com。