觸發器
概述
? ? 觸發器為特殊類型的存儲過程，可在執行語言事件時自動生效。SQL Server 包括三種常規類型的觸發器：DML 觸發器、DDL 觸發器和登錄觸發器。
當服務器或數據庫中發生數據定義語言 (DDL) 事件時將調用 DDL 觸發器。登錄觸發器將為響應 LOGON 事件而激發存儲過程。與 SQL Server 實例建立用戶會話時將引發此事件。
當數據庫中發生數據操作語言 (DML) 事件時將調用 DML 觸發器。DML 事件包括在指定表或視圖中修改數據的 INSERT 語句、UPDATE 語句或 DELETE 語句。DML 觸發器可以查詢其他表，還可以包含復雜的 Transact-SQL 語句。將觸發器和觸發它的語句作為可在觸發器內回滾的單個事務對待。如果檢測到錯誤（例如，磁盤空間不足），則整個事務即自動回滾。
?
步驟
?? 本文主要講述DML觸發器，DML觸發器有兩種：AFTER，INSTEAD OF觸發器，同時DML 觸發器使用 deleted 和 inserted 邏輯（概念）表。 它們在結構上類似于定義了觸發器的表，即對其嘗試執行了用戶操作的表。 在 deleted 和 inserted 表保存了可能會被用戶更改的行的舊值或新值。
- 對于INSERT 操作，inserted保留新增的記錄，deleted無記錄
- 對于DELETE 操作，inserted無記錄，deleted保留被刪除的記錄
- 對于UPDATE操作，inserted保留修改后的記錄，deleted保留修改前的記錄
? ? 一.語法
CREATE TRIGGER [ schema_name . ] trigger_name ON { table | view } [ WITH <dml_trigger_option> [ ,...n ] ] { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } AS { sql_statement [ ; ] [ ,...n ] [ ; ] > }
?
? ? ?二.創建表
CREATE TABLE Class (Cno INT PRIMARY KEY , Cname nvarchar ( 20 ) not null ) go CREATE TABLE Student (SNO INT PRIMARY KEY IDENTITY ( 1 , 1 ), Sname CHAR ( 10 ) not null , Age int not null , Sex char ( 2 ) not null , Cno int NOT NULL ) ALTER TABLE Student ADD CONSTRAINT FK_SNO_Cno FOREIGN KEY (Cno) REFERENCES Class(Cno) go
AFTER觸發器
AFTER 指定 DML 觸發器僅在觸發 SQL 語句中指定的所有操作都已成功執行時才被觸發。 所有的引用級聯操作和約束檢查也必須在激發此觸發器之前成功完成。
如果僅指定 FOR 關鍵字，則 AFTER 為默認值。
不能對視圖定義 AFTER 觸發器
- insert觸發
當向Class表中插入一條數據時，獲取插入的cno，同時向Student表中插入一條數據
IF OBJECT_ID(' TR_Class_insert ','TR') IS NOT NULL
DROP TRIGGER TR_Class_insert
G0
CREATE TRIGGER TR_Class_insert on Class AFTER INSERT AS BEGIN DECLARE @Cno INT SELECT @Cno = Cno FROM inserted -- --獲取插入的數據CNO INSERT INTO Student(Sname,Age,Sex,Cno) VALUES ( ' 李明 ' , 20 , ' 男 ' , @Cno ) END go INSERT INTO Class SELECT 101 , ' 一班 ' SELECT * FROM Class SELECT * FROM Student
- UPDATE觸發
獲取修改的Age值，如果Age為負數則執行回滾操作，否則輸出修改前后的Age值
IF OBJECT_ID ( ' TR_Student_update ' , ' TR ' ) IS NOT NULL DROP TRIGGER TR_Student_update GO CREATE TRIGGER TR_Student_update on Student AFTER UPDATE AS BEGIN DECLARE @Age_old int , @Age_new int SELECT @Age_old = Age from deleted -- --獲取修改前的 SELECT @Age_new = Age FROM inserted -- --獲取更改后的數據 if @Age_new < 0 begin print ' 年齡不能為負數 ' rollback ; end else BEGIN print @Age_old print @Age_new END END go update Student set Age =- 20 where SNO = 1 SELECT * FROM Class SELECT * FROM Student update Student set Age = 25 where SNO = 1 SELECT * FROM Class SELECT * FROM Student
- ?delete觸發
獲取被刪除的數據，返回錯誤提示，該步驟正好驗證了“所有的引用級聯操作和約束檢查也必須在激發此觸發器之前成功完成”，該步驟不會返回制定的錯誤提示，因為被刪除的數據作用于外鍵約束，所以先于觸發器操作執行外鍵約束，返回約束錯誤提示，并執行回滾.
IF OBJECT_ID ( ' TR_Class_delete ' , ' TR ' ) IS NOT NULL DROP TRIGGER TR_Class_delete GO CREATE TRIGGER TR_Class_delete on Class AFTER DELETE AS BEGIN DECLARE @Cno int SELECT @Cno = Cno from DELETED -- -獲取被刪除的記錄 IF @Cno>0 begin RAISERROR ( ' 數據不能被刪除，被用于外鍵約束 ' , 16 , 10 ); rollback -- --執行回滾操作 end END SELECT * FROM Class SELECT * FROM Student DELETE FROM Class where CNO = 101 SELECT * FROM Class SELECT * FROM Student
對Student表建立外鍵約束，用于級聯操作 ON DELETE，對于表的級聯刪除更新操作這里就不講述了
刪除之前創建的外鍵約束,并創建具有級聯更新刪除操作的外鍵約束
alter table student drop constraint FK_SNO_Cno ALTER TABLE Student ADD CONSTRAINT FK_SNO_Cno FOREIGN KEY (Cno) REFERENCES Class (Cno) ON DELETE CASCADE ON UPDATE CASCADE
再執行刪除語句，返回制定錯誤提示“數據不能被刪除，被用于外鍵約束”并執行回滾操作
DELETE FROM Class where CNO = 101 SELECT * FROM Class SELECT * FROM Student
?
INSTEAD OF觸發器
指定執行 DML 觸發器而不是觸發 SQL 語句，因此，其優先級高于觸發語句的操作。? 不能為 DDL 或登錄觸發器指定 INSTEAD OF。
對于表或視圖，每個 INSERT、UPDATE 或 DELETE 語句最多可定義一個 INSTEAD OF 觸發器。? 但是，可以為具有自己的 INSTEAD OF 觸發器的多個視圖定義視圖。
INSTEAD OF 觸發器不可以用于使用 WITH CHECK OPTION 的可更新視圖。? 如果將 INSTEAD OF 觸發器添加到指定了 WITH CHECK OPTION 的可更新視圖中，則 SQL Server 將引發錯誤。 ? 用戶須用 ALTER VIEW 刪除該選項后才能定義 INSTEAD OF 觸發器
對于 INSTEAD OF 觸發器，不允許對具有指定級聯操作 ON DELETE 的引用關系的表使用 DELETE 選項。? 同樣，也不允許對具有指定級聯操作 ON UPDATE 的引用關系的表使用 UPDATE 選項
- ?Insert 觸發
-- -----insert 觸發 -- --刪除已有的instead of觸發器 declare @name nvarchar ( 100 ) select @name = name from sys.triggers where object_name (parent_id) = ' student ' and is_instead_of_trigger = 1 set @name = ' drop trigger ' + @name exec ( @name ) IF OBJECT_ID ( ' TR_Student_instead_insert ' , ' TR ' ) IS NOT NULL DROP TRIGGER TR_Student_instead_insert GO CREATE TRIGGER TR_Student_instead_insert on Student INSTEAD OF insert AS BEGIN SELECT * into T_back from inserted -- --獲取即將插入的數據 END select * from Student select * from Class INSERT INTO Student(Sname,Age,Sex,Cno) values ( ' 張三 ' , 23 , ' 男 ' , 102 ) select * from T_back
?
- ?delete觸發
創建觸發器失敗，因為之前創建外鍵約束時添加了on delete cascade
IF OBJECT_ID ( ' TR_Student_instead_delete ' , ' TR ' ) IS NOT NULL DROP TRIGGER TR_Student_instead_delete GO CREATE TRIGGER TR_Student_instead_delete on Student INSTEAD OF DELETE AS BEGIN DECLARE @Cno int SELECT @Cno = Cno from DELETED -- -獲取被刪除的記錄 IF EXISTS ( SELECT * FROM Class where Cno = @cno ) begin rollback -- --執行回滾操作 RAISERROR ( ' 數據不能被刪除，被用于外鍵約束1 ' , 16 , 10 ); end END 消息 2113 ，級別 16 ，狀態 1 ，過程 TR_Student_instead_delete，第 10 行 因為表 ' Student ' 的 FOREIGN KEY 使用級聯 DELETE 或 UPDATE ，所以無法對該表 創建 INSTEAD OF DELETE 或 INSTEAD OF UPDATE TRIGGER ' TR_Student_instead_delete ' 。
重建外鍵約束，刪除級聯
alter table student drop constraint FK_SNO_Cno ALTER TABLE Student ADD CONSTRAINT FK_SNO_Cno FOREIGN KEY (Cno) REFERENCES Class (Cno)
?
- ?UPDATE觸發
-- ---同一張表中只能定義一個instead of 觸發器，刪除表之前創建的instead of 觸發 declare @name nvarchar ( 100 ) select @name = name from sys.triggers where object_name (parent_id) = ' student ' and is_instead_of_trigger = 1 set @name = ' drop trigger ' + @name exec ( @name ) IF OBJECT_ID ( ' TR_Student_instead_update ' , ' TR ' ) IS NOT NULL DROP TRIGGER TR_Student_instead_update GO CREATE TRIGGER TR_Student_instead_update on Student INSTEAD OF update AS BEGIN DECLARE @Age_del int , @Age_up int SELECT @Age_del = Age from DELETED -- -獲取被更改的記錄 SELECT @Age_up = Age from Inserted begin print @Age_del print @Age_up select * from Student -- --查詢數據是否被更改 end END -- --查詢更新前的表數據 select * from student SNO Sname Age Sex Cno 13 李明 22 男 101 update Student set age = -2 where CNO = 101
----對于前面定義的after觸發器age不能為負數也不會執行，instead of 觸發器高于執行語句，高于after 觸發
SNO Sname Age Sex Cno 13 李明 22 男 101 select * from student SNO Sname Age Sex Cno 13 李明 22 男 101 ( 1 行受影響) 22 -2 ( 1 行受影響) ( 1 行受影響) 當表上面定義了instead of 觸發器，指定執行 DML 觸發器而不是觸發 SQL 語句，因此，其優先級高于觸發語句的操作，而且也不會執行表上面定義的after觸發器
?
?創建帶字段判斷的觸發器， 根據對特定列的 UPDATE 或 INSERT 修改來執行某些操作
-- ----創建字段更新判斷的update觸發器 ALTER TABLE Class ADD Address nvarchar ( 50 ) IF OBJECT_ID ( ' TR_Class_Update ' , ' TR ' ) IS NOT NULL DROP TRIGGER TR_Class_Update GO CREATE TRIGGER TR_Class_Update on Class AFTER UPDATE AS BEGIN IF UPDATE (Cname) or UPDATE (Address) BEGIN RAISERROR ( ' 數據不能被修改 ' , 16 , 10 ) ROLLBACK END END SELECT * FROM Class UPDATE Class set Address = ' 5棟101 ' where Cno = 101 SELECT * FROM Class
?
總結
? ?
雖然觸發器功能強大，輕松可靠地實現許多復雜的功能，同時過多觸發器會造成數據庫及應用程序的維護困難，同時對觸發器過分的依賴，勢必影響數據庫的結構，同時增加了維護的復雜程序.
?
|
備注： ??? 作者： 沉寂的石頭 ??? 博客： http://www.cnblogs.com/chenmh 歡迎大家轉載，但轉載時必須注明文章來源，且在文章開頭明顯處給明鏈接，否則保留追究責任的權利。 歡迎大家拍磚 |
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
