sql觸發(fā)器語法

1、 第七章 觸發(fā)器 本節(jié)要點(diǎn)A 認(rèn)識(shí)觸發(fā)器B 觸發(fā)器的分類C觸發(fā)器的創(chuàng)建D使用觸發(fā)器E 觸發(fā)器練習(xí)的鞏固典型的應(yīng)用就是銀行的取款機(jī)系統(tǒng) 帳戶信息表bank 交易信息表transInfo 張三取錢200 問題：沒有自動(dòng)修改張三的余額最優(yōu)的解決方案就是采用觸發(fā)器：它是一種特殊的存儲(chǔ)過程它是一種特殊的存儲(chǔ)過程 也具備事務(wù)的功能也具備事務(wù)的功能 它能在多表之間執(zhí)行特殊的業(yè)務(wù)規(guī)則它能在多表之間執(zhí)行特殊的業(yè)務(wù)規(guī)則 張三開戶1000元,李四開戶1元 為什么需要觸發(fā)器一、認(rèn)識(shí)觸發(fā)器1、觸發(fā)器的概述定義：觸發(fā)器是在對指定表插入(insert)、更新(update)或刪除(delete)操作時(shí)所自動(dòng)執(zhí)行的存儲(chǔ)過程。

2、作用：實(shí)現(xiàn)由主鍵和外鍵所不能保證的復(fù)雜的參照完整性和數(shù)據(jù)的一致性。 觸發(fā)器是一種高級約束，可以定義比用check 約束更為復(fù)雜的約束 可執(zhí)行復(fù)雜的SQL語句（if/while/case） 可引用其它表中的列 所有數(shù)據(jù)值均正確的狀態(tài)一、認(rèn)識(shí)觸發(fā)器相似：觸發(fā)器與存儲(chǔ)過程相似，觸發(fā)器也是SQL語句集。區(qū)別：觸發(fā)器不能用execute語句調(diào)用，而是在用戶執(zhí)行Transact-SQL語句自動(dòng)執(zhí)行。2、觸發(fā)器與存儲(chǔ)過程的區(qū)別運(yùn)行方式區(qū)別：存儲(chǔ)過程用戶應(yīng)用程序或觸發(fā)器調(diào)用并執(zhí)行觸發(fā)器特定事件，自動(dòng)執(zhí)行二、觸發(fā)器的分類1、DDL觸發(fā)器定義：當(dāng)服務(wù)器或者數(shù)據(jù)庫中發(fā)生數(shù)據(jù)定義語言（DDL）時(shí)觸發(fā)器將被調(diào)用。DDL

3、數(shù)據(jù)定義語言：創(chuàng)建，修改或刪除數(shù)據(jù)庫中各種對象，包括表，視圖，索引等。以下操作可以使用DDL觸發(fā)器：1、要防止對數(shù)據(jù)庫架構(gòu)進(jìn)行某些修改 3、要記錄數(shù)據(jù)架構(gòu)中的更改或者事件。 2、希望數(shù)據(jù)庫中發(fā)生某種情況以響應(yīng)數(shù)據(jù)庫架構(gòu)中的修改二、觸發(fā)器的分類1、DML觸發(fā)器定義：當(dāng)數(shù)據(jù)庫服務(wù)器中發(fā)生數(shù)據(jù)操作語言（DML）事件時(shí)要執(zhí)行的操作。DML數(shù)據(jù)定義語言：對已存在數(shù)據(jù)庫表進(jìn)行元組插入，刪除，修改。2、DML觸發(fā)器的用途( 1 ) DML觸發(fā)器可通過數(shù)據(jù)庫中相關(guān)表實(shí)現(xiàn)級聯(lián)修改，通過級聯(lián)引用完整性約束可以更有效的更改。( 2 )DML觸發(fā)器可以防止惡意或者錯(cuò)誤insert，update，以及delete操作，

4、并強(qiáng)制執(zhí)行比check約束 定義的限制更為復(fù)雜的其他限制。( 3 )DML觸發(fā)器可以評估數(shù)據(jù)修改前后表的狀態(tài)，并根據(jù)該差異采取措施。( 4 )一個(gè)表的多個(gè)同類DML觸發(fā)器，允許采取多個(gè)不同的操作來響應(yīng)同一個(gè)修改語句。二、觸發(fā)器的分類 觸發(fā)器觸發(fā)時(shí) 創(chuàng)建了DELETED表和INSERED表 只讀，不允許修改；觸發(fā)器執(zhí)行完成后，自動(dòng)刪除1、delete表 delete表存放由執(zhí)行delete或者update語句而要從表中刪除所有行，在執(zhí)行delete或update被刪除的行從觸發(fā)觸發(fā)器表中移動(dòng)到delete表中。2、insert表insert表存放由執(zhí)行insert或者update語句而要向表中插

5、入的所有的行，在執(zhí)行insert或update新的行同時(shí)添加到觸發(fā)器的表和insert表中，insert表的內(nèi)容是觸發(fā)觸發(fā)器的表新行的副本。二、觸發(fā)器的分類修改操作inserted表deleted表增加增加(INSERT)(INSERT)記錄記錄存放新增的記錄存放新增的記錄-刪除刪除(DELETE)(DELETE)記錄記錄-存放被刪除的記錄存放被刪除的記錄修改修改(UPDATE)(UPDATE)記錄記錄存放更新后的記錄存放更新后的記錄存放更新前的記錄存放更新前的記錄inserted表和deleted表存放的信息三、觸發(fā)器創(chuàng)建語法create trigger 觸發(fā)器名稱on 表名 / 視圖名fo

6、r / after /insted of -觸發(fā)的時(shí)機(jī)delete/insert/update -觸發(fā)器的類型assql語句end例：在student數(shù)據(jù)庫的學(xué)員信息表表中 創(chuàng)建一個(gè)stuinfo_delete觸發(fā)器create trigger stuinfo_deleteon 學(xué)員信息表for delete asdelete 成績表from 成績表 a , deleted bwhere a.學(xué)號=b.學(xué)號四、DML觸發(fā)器1、insert觸發(fā)器create trigger stuinfo_insert -創(chuàng)建觸發(fā)器on 學(xué)生表after insert asupdate stuinfo -插入年

7、齡set 年齡=datediff（year，s.生日，getdate（）from 學(xué)生表 a inner join inserted bwhere a.學(xué)號=b.學(xué)號-after 之insert觸發(fā)器insert觸發(fā)器對目標(biāo)表執(zhí)行insert語句時(shí)，就會(huì)調(diào)用觸發(fā)器。例如我們添加一個(gè)年齡列，這個(gè)觸發(fā)器的名稱為stuinfo_insert插入一個(gè)新的學(xué)員信息，以驗(yàn)證觸發(fā)器是否執(zhí)行insert into stuinfo value （0002, mike, 男, 1993-01-25, null）select *from stuinfotransInfocardIDtransType transMo

8、ney1001 00021001 0002存入 300存入 500insertedcardIDtransType transMoney1001 0001支取 200transInfocardIDtransType transMoney1001 00021001 0002存入 300300存入 5005001001 0001支取 200插入記錄行觸發(fā)insert觸發(fā)器。向inserted表中插入新行的副本觸發(fā)器檢查inserted表中插入的新行數(shù)據(jù)，確定是否需要回滾或執(zhí)行其他操作INSERT觸發(fā)器的工作原理：觸發(fā)器的工作原理：四、DML觸發(fā)器1、delete觸發(fā)器定義：當(dāng)觸發(fā)delete觸發(fā)器時(shí)

9、，從表中刪除的行將會(huì)放置在一個(gè)特殊的delete表中，delete跟deleted表一樣是個(gè)臨時(shí)表。區(qū)別：delete觸發(fā)器并不執(zhí)行truncate table語句，原因在于日志不記錄 truncate table例題：刪除數(shù)據(jù)庫【student】中【學(xué)員信息】表時(shí)，相應(yīng)的考試成績備份表也會(huì)刪除,使用delete語句刪除002這個(gè)學(xué)員信息，驗(yàn)證觸發(fā)器就會(huì)自動(dòng)執(zhí)行，并刪除學(xué)員成績備份表中的信息create trigger stuinfo_delete -創(chuàng)建觸發(fā)器on 學(xué)員信息for delete asdelete 成績表from 成績表 a , deleted bwhere a.學(xué)號=b.學(xué)號

10、insert 成績表 values（0002 ,90,95）select *from 成績表 delete from 學(xué)員信息 where stuno=0001select * from 成績表transInfocardIDtransType transMoney存入 300存入 5001001 0001支取 200transInfocardIDtransType transMoney1001 00021001 0002存入 300存入 500deletedcardIDtransType transMoney1001 0001支取 200刪除記錄行觸發(fā)delete觸發(fā)器向deleted表中插入

11、被刪除的副本觸發(fā)器檢查deleted表中被刪除的數(shù)據(jù)，決定是否需要回滾或執(zhí)行其他操作DELETE觸發(fā)器的工作原理：觸發(fā)器的工作原理：四、DML觸發(fā)器update觸發(fā)器定義：當(dāng)一個(gè)update語句在目標(biāo)表運(yùn)行的時(shí)候就調(diào)用更新觸發(fā)器，例題：數(shù)據(jù)庫【student】中【stuinfo】表中生產(chǎn)日期的改變，對應(yīng)學(xué)員的年齡也發(fā)生改變create trigger stuinfo_update -創(chuàng)建觸發(fā)器on stuinfoafter stuinfo asif update(birthday) beginupdate stuinfo -執(zhí)行SQL語句set stuage=datediff(year,s.b

12、irthday,getdate()from stuinfo s inner join insertd ion s.stuno=i.stunoend四、DDL觸發(fā)器定義：當(dāng)服務(wù)器或者數(shù)據(jù)庫中發(fā)生數(shù)據(jù)定義語言（DDL）時(shí)觸發(fā)器將被調(diào)用。DDL數(shù)據(jù)定義語言：創(chuàng)建，修改或刪除數(shù)據(jù)庫中各種對象，包括表，視圖，索引等。例題：如何使用DDL觸發(fā)器來防止在【student】數(shù)據(jù)庫中表被修改刪除的操作，首先要在 【student】數(shù)據(jù)庫中定義一個(gè)數(shù)據(jù)庫的DDL觸發(fā)器。create trigger TRIG_DDL -創(chuàng)建一個(gè)DDL觸發(fā)器for drop_table,drop_view -刪除表或刪除視圖on d

13、atabase -數(shù)據(jù)庫asbegin print 無法修改或者刪除表，請?jiān)谥敖没騽h除DDL觸發(fā)器TRIG_DDL！ ROLLBACK TRANSACTION -采用回滾的辦法以達(dá)到阻止操作的目的end 嵌套觸發(fā)器 如果一個(gè)觸發(fā)器在執(zhí)行操作時(shí)引發(fā)了另一個(gè)觸發(fā)器，而這個(gè)觸發(fā)器又接著引發(fā)下一個(gè)觸發(fā)器，那么就形成了觸發(fā)器的嵌套。任何觸發(fā)器都可以包含影響另一個(gè)表的UPDATE、INSERT或者DELETE語句。嵌套觸發(fā)器在安裝時(shí)就被啟用嵌套觸發(fā)器在安裝時(shí)就被啟用可以使用系統(tǒng)存儲(chǔ)過程可以使用系統(tǒng)存儲(chǔ)過程sp_configuresp_configure禁用和重新啟用嵌套禁用和重新啟用嵌套觸發(fā)器最多可以

14、嵌套觸發(fā)器最多可以嵌套3232層層禁用嵌套禁用嵌套EXEC sp_configure nested triggers,0啟用嵌套啟用嵌套EXEC sp_configure nested triggers,1管理觸發(fā)器查看觸發(fā)器：查看觸發(fā)器：可以使用像sp_helptext、sp_help和sp_depends等系統(tǒng)存儲(chǔ)過程來查看觸發(fā)器的有關(guān)信息修改觸發(fā)器：修改觸發(fā)器：修改觸發(fā)器的語法與創(chuàng)建觸發(fā)器的語法一樣，只要把create改為alter即可刪除觸發(fā)器：刪除觸發(fā)器：禁用禁用DML觸發(fā)器：觸發(fā)器：禁用禁用DDL觸發(fā)器：觸發(fā)器：禁用所有觸發(fā)器：禁用所有觸發(fā)器：啟用觸發(fā)器：啟用觸發(fā)器：只要將DISA

15、BLE變?yōu)镋NABLE即可exec sp_helptext TRIG_DDL -查看觸發(fā)器的內(nèi)容Alter trigger TRIG_DDL drop trigger TRIG_DDLDISABLE TRIGGER tri_stuinfo_update ON stuInfoDISABLE TRIGGER TRIG_DDL ON DATABASEDISABLE TRIGGER ALL ON ALL SERVERENABLE TRIGGER tri_stuinfo_update ON stuInfo -啟用觸發(fā)器ENABLE TRIGGER trig_DDL ON DATABASE -啟用數(shù)據(jù)庫觸

16、發(fā)器ENABLE TRIGGER ALL ON ALL SERVER -啟用以同一作用域定義的所有觸發(fā)器不能在觸發(fā)器中使用的語句不能在觸發(fā)器中使用的語句 觸發(fā)器中可以使用大多數(shù)觸發(fā)器中可以使用大多數(shù) T-SQL T-SQL 語句，語句，但如下一些語句是不能在觸發(fā)器中使用的。但如下一些語句是不能在觸發(fā)器中使用的。CREATE 語句，如：CREATE DATABASE、CREATE TABLE、CREATE INDEX 等。ALTER 語句，如：ALTER DATABASE、ALTER TABLE、ALTER INDEX 等。DROP 語句，如：DROP DATABASE、DROP TABLE、DROP INDEX 等。DISK 語句，如：DISK INIT、DISK RESIZE。LOAD 語句，如：LOAD DATABASE、LOAD LOG。RESTORE 語句，如：RESTORE DATABASE、RESTORE LOG。RECONFIGURETRUNCATE TABLE 語句在sybase的觸發(fā)器中不可使用！觸發(fā)器是在對表進(jìn)行插入、更新或刪除操作時(shí)自動(dòng)執(zhí)行的存儲(chǔ)過程，觸發(fā)器是在對表進(jìn)行插入、更新或刪除操作時(shí)自動(dòng)執(zhí)行的存儲(chǔ)過程，觸發(fā)器通常用于強(qiáng)制業(yè)務(wù)規(guī)則觸發(fā)器通常用于強(qiáng)制業(yè)務(wù)規(guī)則觸發(fā)器還是一個(gè)特殊的事務(wù)單元，當(dāng)出現(xiàn)錯(cuò)誤時(shí)，可以執(zhí)行觸發(fā)器還是一個(gè)特殊的事務(wù)單元，當(dāng)出現(xiàn)