SQL?SERVER觸發(fā)器怎么創(chuàng)建

本篇內(nèi)容主要講解“SQL SERVER觸發(fā)器怎么創(chuàng)建”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實(shí)用性強(qiáng)。下面就讓小編來帶大家學(xué)習(xí)“SQL SERVER觸發(fā)器怎么創(chuàng)建”吧!

觸發(fā)器是一種特殊的存儲過程，觸發(fā)器主要是通過事件進(jìn)行觸發(fā)而被自動調(diào)用執(zhí)行，而存儲過程必須通過存儲過程的名稱被調(diào)用。

一、觸發(fā)器的定義

觸發(fā)器是在對表進(jìn)行插入、更新或刪除操作時自動執(zhí)行的特殊存儲過程。觸發(fā)器通常用于強(qiáng)制業(yè)務(wù)規(guī)則，觸發(fā)器是一種高級約束，可以定義比CHECK約束更為復(fù)雜的約束：可以執(zhí)行復(fù)雜的SQL語句（if/while/case）,可以引用其他表中的列。觸發(fā)器定義在特定的表上，與表相關(guān)，自動觸發(fā)執(zhí)行，不能直接調(diào)用，是一個事務(wù)（可回滾）。

二、觸發(fā)器分類

SQL SERVER中觸發(fā)器可以分為兩類：DML觸發(fā)器和DDL觸發(fā)器，DML觸發(fā)器針對表，DDL觸發(fā)器會影響多種數(shù)據(jù)定義語言語句而觸發(fā)，這些語句有create、alter、drop語句。

DML觸發(fā)器分為：

1、after觸發(fā)器（之后觸發(fā)）

• a、insert觸發(fā)器

• b、update觸發(fā)器

• c、delete觸發(fā)器

2、instead of觸發(fā)器（之前觸發(fā)）

after觸發(fā)器要求只有執(zhí)行某一操作（insert、update、delete）之后觸發(fā)器才能被觸發(fā)，且只能定義在表上。而instead of觸發(fā)器表示并不執(zhí)行其定義的操作（insert、update、delete）而僅是執(zhí)行觸發(fā)器本身，其優(yōu)先級高于觸發(fā)語句的執(zhí)行。

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

這兩張表是邏輯表也是虛表，觸發(fā)器觸發(fā)時系統(tǒng)自動在內(nèi)存中創(chuàng)建這兩張表，不會存儲在數(shù)據(jù)庫中。這兩張表都是只讀的，不允許修改。這兩張表的結(jié)果總是與被觸發(fā)器應(yīng)用的表的結(jié)構(gòu)相同。當(dāng)觸發(fā)器完成工作后，這兩張表就會被刪除。inserted表臨時保存了插入或更新后的記錄行，可以從inserted表中檢查插入的數(shù)據(jù)是否滿足業(yè)務(wù)需求，如果不滿足，則向用戶報告錯誤消息，并回滾插入操作。deleted表臨時保存了刪除或更新前的記錄行，可以從deleted表中檢查被刪除的數(shù)據(jù)是否滿足業(yè)務(wù)需求，如果不滿足，則向用戶報告錯誤消息，并回滾刪除操作。update數(shù)據(jù)的時候是先刪除表記錄，然后插入一條記錄，在inserted和deleted表就都有update后的數(shù)據(jù)記錄了。

  inserted表和deleted表存放的信息：

三、創(chuàng)建觸發(fā)器

語法：

     CREATE TRIGGER trigger_name

     ON table_name

     [WITH ENCRYPTION]

     FOR [DELETE, INSERT, UPDATE]

     AS

       T-SQL語句

     GO

WITH ENCRYPTION表示加密觸發(fā)器定義的SQL文本

DELETE, INSERT, UPDATE指定觸發(fā)器的類型

1、創(chuàng)建insert類型的觸發(fā)器

插入觸發(fā)器

--GradeInfo表中插入一條數(shù)據(jù)，MyStudentInfo表中插入一條記錄
IF (object_id('tr_insert','tr') is not null)
    drop trigger tr_insert
GO
CREATE trigger tr_insert
on GradeInfo
after insert --插入觸發(fā)
as
 begin
   --定義變量
   declare @GradeId int
   --在inserted表中查詢已經(jīng)插入記錄信息
   select @GradeId=id from INSERTED
   --MyStudentInfo表中插入數(shù)據(jù)
   insert INTO MyStudentInfo (GradeId) VALUES (@GradeId)
   print '插入成功!'
 end

插入數(shù)據(jù)

insert INTO GradeInfo VALUES (11,'C++')

查詢數(shù)據(jù)

select * from MyStudentInfo where GradeId=11

2、delete觸發(fā)器

刪除MyStudentInfo表中的數(shù)據(jù)，插入備份表

--刪除MyStudentInfo表中的數(shù)據(jù)，插入備份表
 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 '正在備份數(shù)據(jù)......'
 IF (object_id('MyStudentInfo_Back','U') is not null)
 --存在表，直接插入數(shù)據(jù)
   insert INTO MyStudentInfo_Back SELECT * from DELETED
 else
   select * into MyStudentInfo_Back from DELETED
 PRINT '備份完成'
  end

刪除前查詢MyStudentInfo表數(shù)據(jù)

select * from MyStudentInfo

刪除id=9的數(shù)據(jù)

delete FROM MyStudentInfo where Id=9

查詢備份表數(shù)據(jù)

select * from MyStudentInfo_Back

3、update觸發(fā)器

  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更新列級觸發(fā)器

 --update更新列級觸發(fā)器
   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 '系統(tǒng)提示：主鍵ID不能更新'
     rollback
   end
  end

更新id列

update GradeInfo SET Id=15 where Id=4

4、instead of觸發(fā)器

instead of觸發(fā)器表示并不執(zhí)行其定義的操作（insert、update、delete）而僅是執(zhí)行觸發(fā)器本身的內(nèi)容，其優(yōu)先級高于定義的SQL語句的執(zhí)行

語法：

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

創(chuàng)建instead of觸發(fā)器

--創(chuàng)建instead of觸發(fā)器
/*MyStudentInfo表里面插入數(shù)據(jù)之前，先判斷GradeInfo表中是否有對應(yīng)的班級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觸發(fā)器

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

測試刪除表

drop TABLE MyStudentInfo

測試修改表結(jié)構(gòu)

alter table MyStudentInfo
alter column Name varchar(32)

禁用DML觸發(fā)器

disable trigger tr_insteadOf on MyStudentInfo

啟用DML觸發(fā)器

enable trigger tr_insteadOf on MyStudentInfo

禁用DDL觸發(fā)器

disable trigger tr_DDL on database

啟用DDL觸發(fā)器

enable trigger tr_DDL on database

到此，相信大家對“SQL SERVER觸發(fā)器怎么創(chuàng)建”有了更深的了解，不妨來實(shí)際操作一番吧！這里是億速云網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！