共計 6375 個字符，預計需要花費 16 分鐘才能閱讀完成。

這篇文章將為大家詳細講解有關如何深入淺析 SQL Server 觸發(fā)器，文章內容質量較高，因此丸趣 TV 小編分享給大家做個參考，希望大家閱讀完這篇文章后對相關知識有一定的了解。

觸發(fā)器是一種特殊類型的存儲過程，它不同于之前的我們介紹的存儲過程。觸發(fā)器主要是通過事件進行觸發(fā)被自動調用執(zhí)行的。而存儲過程可以通過存儲過程的名稱被調用。

  什么是觸發(fā)器

觸發(fā)器對表進行插入、更新、刪除的時候會自動執(zhí)行的特殊存儲過程。觸發(fā)器一般用在 check 約束更加復雜的約束上面。觸發(fā)器和普通的存儲過程的區(qū)別是：觸發(fā)器是當對某一個表進行操作。諸如：update、insert、delete 這些操作的時候，系統(tǒng)會自動調用執(zhí)行該表上對應的觸發(fā)器。SQL Server 2005 中觸發(fā)器可以分為兩類：DML 觸發(fā)器和 DDL 觸發(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ā)器本身。既可以在表上定義 instead of 觸發(fā)器，也可以在視圖上定義。

觸發(fā)器有兩個特殊的表：插入表（instered 表）和刪除表（deleted 表）。這兩張是邏輯表也是虛表。有系統(tǒng)在內存中創(chuàng)建者兩張表，不會存儲在數(shù)據(jù)庫中。而且兩張表的都是只讀的，只能讀取數(shù)據(jù)而不能修改數(shù)據(jù)。這兩張表的結果總是與被改觸發(fā)器應用的表的結構相同。當觸發(fā)器完成工作后，這兩張表就會被刪除。Inserted 表的數(shù)據(jù)是插入或是修改后的數(shù)據(jù)，而 deleted 表的數(shù)據(jù)是更新前的或是刪除的數(shù)據(jù)。

Update 數(shù)據(jù)的時候就是先刪除表記錄，然后增加一條記錄。這樣在 inserted 和 deleted 表就都有 update 后的數(shù)據(jù)記錄了。注意的是：觸發(fā)器本身就是一個事務，所以在觸發(fā)器里面可以對修改數(shù)據(jù)進行一些特殊的檢查。如果不滿足可以利用事務回滾，撤銷操作。

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

語法

create trigger tgr_nameon table_namewith encrypion –加密觸發(fā)器  for update...as Transact-SQL #  創(chuàng)建 insert 類型觸發(fā)器 -- 創(chuàng)建 insert 插入類型觸發(fā)器 if (object_id( tgr_classes_insert ,  tr) is not null) drop trigger tgr_classes_insertgocreate trigger tgr_classes_inserton classes for insert -- 插入觸發(fā) as -- 定義變量  declare @id int, @name varchar(20), @temp int; -- 在 inserted 表中查詢已經(jīng)插入記錄信息  select @id = id, @name = name from inserted; set @name = @name + convert(varchar, @id); set @temp = @id / 2; insert into student values(@name, 18 + @id, @temp, @id); print  添加學生成功！go-- 插入數(shù)據(jù) insert into classes values(5 班 , getDate());-- 查詢數(shù)據(jù) select * from classes;select * from student order by id; insert 觸發(fā)器，會在 inserted 表中添加一條剛插入的記錄。 #  創(chuàng)建 delete 類型觸發(fā)器 --delete 刪除類型觸發(fā)器 if (object_id( tgr_classes_delete ,  TR) is not null) drop trigger tgr_classes_deletegocreate trigger tgr_classes_deleteon classes for delete -- 刪除觸發(fā) as print  備份數(shù)據(jù)中……  if (object_id( classesBackup ,  U) is not null) -- 存在 classesBackup，直接插入數(shù)據(jù)  insert into classesBackup select name, createDate from deleted; else -- 不存在 classesBackup 創(chuàng)建再插入  select * into classesBackup from deleted; print  備份數(shù)據(jù)成功！go---- 不顯示影響行數(shù) --set nocount on;delete classes where name =  5 班 -- 查詢數(shù)據(jù) select * from classes;select * from classesBackup; delete 觸發(fā)器會在刪除數(shù)據(jù)的時候，將剛才刪除的數(shù)據(jù)保存在 deleted 表中。 #  創(chuàng)建 update 類型觸發(fā)器 --update 更新類型觸發(fā)器 if (object_id( tgr_classes_update ,  TR) is not null) drop trigger tgr_classes_updategocreate trigger tgr_classes_updateon classes for updateas declare @oldName varchar(20), @newName varchar(20); -- 更新前的數(shù)據(jù)  select @oldName = name from deleted; if (exists (select * from student where name like  % + @oldName +  %)) begin -- 更新后的數(shù)據(jù)  select @newName = name from inserted; update student set name = replace(name, @oldName, @newName) where name like  % + @oldName +  %  print  級聯(lián)修改數(shù)據(jù)成功！ end else print  無需修改 student 表！go-- 查詢數(shù)據(jù) select * from student order by id;select * from classes;update classes set name =  五班  where name =  5 班  update 觸發(fā)器會在更新數(shù)據(jù)后，將更新前的數(shù)據(jù)保存在 deleted 表中，更新后的數(shù)據(jù)保存在 inserted 表中。 # update 更新列級觸發(fā)器 if (object_id( tgr_classes_update_column ,  TR) is not null) drop trigger tgr_classes_update_columngocreate trigger tgr_classes_update_columnon classes for updateas -- 列級觸發(fā)器：是否更新了班級創(chuàng)建時間  if (update(createDate)) begin raisError(系統(tǒng)提示：班級創(chuàng)建時間不能修改！, 16, 11); rollback tran; endgo-- 測試 select * from student order by id;select * from classes;update classes set createDate = getDate() where id = 3;update classes set name =  四班  where id = 7;  更新列級觸發(fā)器可以用 update 是否判斷更新列記錄； # instead of 類型觸發(fā)器  instead of 觸發(fā)器表示并不執(zhí)行其定義的操作（insert、update、delete）而僅是執(zhí)行觸發(fā)器本身的內容。  創(chuàng)建語法 create trigger tgr_nameon table_namewith encryption instead of update...as T-SQL #  創(chuàng)建 instead of 觸發(fā)器 if (object_id( tgr_classes_inteadOf ,  TR) is not null) drop trigger tgr_classes_inteadOfgocreate trigger tgr_classes_inteadOfon classes instead of delete/*, update, insert*/as declare @id int, @name varchar(20); -- 查詢被刪除的信息，病賦值  select @id = id, @name = name from deleted; print  id:   + convert(varchar, @id) +  , name:   + @name; -- 先刪除 student 的信息  delete student where cid = @id; -- 再刪除 classes 的信息  delete classes where id = @id; print  刪除 [ id:   + convert(varchar, @id) +  , name:   + @name +   ]  的信息成功！go--testselect * from student order by id;select * from classes;delete classes where id = 7; #  顯示自定義消息 raiserrorif (object_id( tgr_message ,  TR) is not null) drop trigger tgr_messagegocreate trigger tgr_messageon student after insert, updateas raisError(tgr_message 觸發(fā)器被觸發(fā) , 16, 10);go--testinsert into student values(lily , 22, 1, 7);update student set sex = 0 where name =  lucy select * from student order by id; #  修改觸發(fā)器 alter trigger tgr_messageon studentafter deleteas raisError(tgr_message 觸發(fā)器被觸發(fā) , 16, 10);go--testdelete from student where name =  lucy  #  啟用、禁用觸發(fā)器 -- 禁用觸發(fā)器 disable trigger tgr_message on student;-- 啟用觸發(fā)器 enable trigger tgr_message on student; #  查詢創(chuàng)建的觸發(fā)器信息 -- 查詢已存在的觸發(fā)器 select * from sys.triggers;select * from sys.objects where type =  TR -- 查看觸發(fā)器觸發(fā)事件 select te.* from sys.trigger_events te join sys.triggers ton t.object_id = te.object_idwhere t.parent_class = 0 and t.name =  tgr_valid_data -- 查看創(chuàng)建觸發(fā)器語句 exec sp_helptext  tgr_message  #  示例，驗證插入數(shù)據(jù) if ((object_id( tgr_valid_data ,  TR) is not null)) drop trigger tgr_valid_datagocreate trigger tgr_valid_dataon studentafter insertas declare @age int, @name varchar(20); select @name = s.name, @age = s.age from inserted s; if (@age   18) begin raisError(插入新數(shù)據(jù)的 age 有問題 , 16, 1); rollback tran; endgo--testinsert into student values(forest , 2, 0, 7);insert into student values(forest , 22, 0, 7);select * from student order by id; #  示例，操作日志 if (object_id( log ,  U) is not null) drop table loggocreate table log( id int identity(1, 1) primary key, action varchar(20), createDate datetime default getDate())goif (exists (select * from sys.objects where name =  tgr_student_log)) drop trigger tgr_student_loggocreate trigger tgr_student_logon studentafter insert, update, deleteas if ((exists (select 1 from inserted)) and (exists (select 1 from deleted))) begin insert into log(action) values(updated  end else if (exists (select 1 from inserted) and not exists (select 1 from deleted)) begin insert into log(action) values(inserted  end else if (not exists (select 1 from inserted) and exists (select 1 from deleted)) begin insert into log(action) values(deleted  endgo--testinsert into student values( king , 22, 1, 7);update student set sex = 0 where name =  king delete student where name =  king select * from log;select * from student order by id;

關于如何深入淺析 SQL Server 觸發(fā)器就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。