● 原因
● 觸發(fā)器
○ 簡介
○ 分類
○ INSERTED和DELETED
○ 優(yōu)缺點
● 語法
○ 建立觸發(fā)器
○ 刪除觸發(fā)器
○ 修改觸發(fā)器
○ 開啟和禁用
○ 提醒和保護(hù)
● 示例
原因
今天看教程再次提及觸發(fā)器，以前看數(shù)據(jù)庫視頻、牛腩視頻、天轟穿都講到過觸發(fā)器，而只知道觸發(fā)器的幾個簡單應(yīng)用，感覺學(xué)的甚差，所以借此機(jī)會仔細(xì)學(xué)學(xué)觸發(fā)器。
觸發(fā)器
觸發(fā)器簡介：
觸發(fā)器（trigger）是個特殊的存儲過程，它的執(zhí)行不是由程序調(diào)用，也不是手工啟動，而是由事件來觸發(fā)，當(dāng)對一個表進(jìn)行操作（ insert，delete， update）時就會激活它執(zhí)行，觸發(fā)器經(jīng)常用于加強(qiáng)數(shù)據(jù)的完整性約束和業(yè)務(wù)規(guī)則等。在我看來觸發(fā)器實際上就是一個事件，就像C#中，點擊一個按鈕會觸發(fā)相應(yīng)的操作。
觸發(fā)器的分類：
（1）DML（ 數(shù)據(jù)操縱語言 Data Manipulation Language）觸發(fā)器：是指觸發(fā)器在數(shù)據(jù)庫中發(fā)生DML事件時將啟用。DML事件即指在表或視圖中修改數(shù)據(jù)的insert、update、delete語句。
（2）DDL（數(shù)據(jù)定義語言 Data Definition Language）觸發(fā)器：是指當(dāng)服務(wù)器或數(shù)據(jù)庫中發(fā)生（DDL事件時將啟用。DDL事件即指在表或索引中的create、alter、drop語句也。
（3）登陸觸發(fā)器：是指當(dāng)用戶登錄SQL SERVER實例建立會話時觸發(fā)。
其中DML觸發(fā)器最為常用，根據(jù)DML觸發(fā)器觸發(fā)的方式不同又分為以下兩種情況：
（1）AFTER觸發(fā)器：它是在執(zhí)行INSERT、UPDATE、DELETE語句操作之后執(zhí)行觸發(fā)器操作。它主要是用于記錄變更后的處理或檢查，一旦發(fā)生錯誤，可以用Rollback Transaction語句來回滾本次扣件，不過不能對視圖定義AFTER觸發(fā)器。
（2）INSTEAD OF觸發(fā)器：它在執(zhí)行INSERT、UPDATE、DELETE語句操作之前執(zhí)行觸發(fā)器本身所定義的操作。而INSTEAD OF觸發(fā)器是可以定義在視圖上的。
INSERTED和DELETED
在SQL SERVER 2008中，DML觸發(fā)器的實現(xiàn)使用兩個邏輯表DELETED和INSERTED。這兩個表是建立在數(shù)據(jù)庫服務(wù)器的內(nèi)存中，我們只有只讀的權(quán)限。DELETED和INSERED表的結(jié)構(gòu)和觸發(fā)器所在的數(shù)據(jù)表的結(jié)構(gòu)是一樣的。當(dāng)觸發(fā)器執(zhí)行完成后，它們也就會被自動刪除：INSERED表用于存放你在操件insert、update、delete語句后，更新的記錄。比如你插入一條數(shù)據(jù)，那么就會把這條記錄插入到INSERTED表：DELETED表用于存放你在操作 insert、update、delete語句前，你創(chuàng)建觸發(fā)器表中數(shù)據(jù)庫。比如你原來的表中有三條數(shù)據(jù)，那么他也有三條數(shù)據(jù)。也就是說，我們可以使用這兩個臨時的駐留內(nèi)存的表，測試某些數(shù)據(jù)修改的效果及設(shè)置觸發(fā)器操作的條件。
觸發(fā)器的優(yōu)缺點：
觸發(fā)器可通過數(shù)據(jù)庫中的相關(guān)表實現(xiàn)級聯(lián)更改，可以強(qiáng)制比用CHECK約束定義的約束更為復(fù)雜的約束。與 CHECK 約束不同，觸發(fā)器可以引用其它表中的列，例如觸發(fā)器可以使用另一個表中的 SELECT 比較插入或更新的數(shù)據(jù)，以及執(zhí)行其它操作。觸發(fā)器也可以根據(jù)數(shù)據(jù)修改前后的表狀態(tài)，再行采取對策。一個表中的多個同類觸發(fā)器（INSERT、UPDATE 或 DELETE）允許采取多個不同的對策以響應(yīng)同一個修改語句。
與此同時，雖然觸發(fā)器功能強(qiáng)大，輕松可靠地實現(xiàn)許多復(fù)雜的功能，為什么又要慎用？過多觸發(fā)器會造成數(shù)據(jù)庫及應(yīng)用程序的維護(hù)困難，同時對觸發(fā)器過分的依賴，勢必影響數(shù)據(jù)庫的結(jié)構(gòu)，同時增加了維護(hù)的復(fù)雜程序。
語法
建立觸發(fā)器
CREATE TRIGGER 觸發(fā)器名稱
ON 表名
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ DELETE ] [ , ]
[UPDATE ] }
AS
SQL 語句 [ ... n ] |
刪除觸發(fā)器：
| DROP TRIGGER 觸發(fā)器名 [ , ... n ] |
修改觸發(fā)器：
ALTER TRIGGER 觸發(fā)器名稱
ON 表名
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ DELETE ] [ , ]
[UPDATE ] }
AS
SQL 語句 [ ... n ] |
開啟和禁用：
disable trigger trigDB on database --禁用觸發(fā)器
enable trigger trigDB on database --開啟觸發(fā)器 |
提醒和保護(hù)：
print '刪除了觸發(fā)器***'
raiserror('數(shù)據(jù)一致性驗證',16,1)
rollback transaction |
示例
在S表創(chuàng)建UPDATE觸發(fā)器：
Create trigger tri_Updates
on s
for update
as
print 'the table s was updated' |
禁止刪除SC表中成績不及格學(xué)生的記錄：
CREATE TRIGGER tri_del_grade
ON SC FOR DELETE
AS
IF EXISTS(SELECT * FROM DELETED
WHERE Grade < 60)
ROLLBACK |
禁止將SC表中不及格學(xué)生的成績改為及格：
create trigger tri_update_grade
on sc for update
as
if update(grade)
if exists(select * from inserted,deleted
where inserted.sno=deleted.sno and inserted.grade>=60 and deleted.grade<60)
begin
print '不能將不及格的成績改為及格'
rollback
end |