SQL?SERVER觸發器怎么創建

本篇內容主要講解“SQL SERVER觸發器怎么創建”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“SQL SERVER觸發器怎么創建”吧!

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

一、觸發器的定義

觸發器是在對表進行插入、更新或刪除操作時自動執行的特殊存儲過程。觸發器通常用于強制業務規則，觸發器是一種高級約束，可以定義比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表存放的信息：

三、創建觸發器

語法：

     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觸發器怎么創建”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！