使用SQL語句創(chuàng)建觸發(fā)器

“超市供銷存系統(tǒng)”的數(shù)據(jù)庫實(shí)施和管理

創(chuàng)建觸發(fā)器任務(wù)8：創(chuàng)建觸發(fā)器理解事務(wù)并能靈活使用；理解觸發(fā)器的作用和分類；能創(chuàng)建INSERT觸發(fā)器；能創(chuàng)建UPDATE觸發(fā)器；能創(chuàng)建DELETE觸發(fā)器。任務(wù)描述一、事務(wù)事務(wù)(Transaction)是用戶定義的一個(gè)數(shù)據(jù)庫操作序列，這些操作要么全做，要么全不做，是一個(gè)不可分割的工作單位。如果某一事務(wù)成功，則在該事務(wù)中進(jìn)行的所有數(shù)據(jù)更改均會(huì)提交，成為數(shù)據(jù)庫中的永久組成部分。如果事務(wù)遇到錯(cuò)誤且必須取消或回滾，則所有數(shù)據(jù)更改均被清除。1.顯式事務(wù)每個(gè)事務(wù)均以BEGINTRANSACTION語句顯式開始，以COMMIT或ROLLBACK語句顯式結(jié)束。

BEGINTRANSACTION

BEGINTRANSACTIONSQL語句1SQL語句1

SQL語句2SQL語句2……….……….

COMMITROLLBACK事務(wù)結(jié)束COMMIT：事務(wù)正常結(jié)束提交事務(wù)的所有操作事務(wù)中所有對數(shù)據(jù)庫的更新永久生效ROLLBACK：事務(wù)異常終止事務(wù)運(yùn)行的過程中發(fā)生了故障，不能繼續(xù)執(zhí)行回滾事務(wù)的所有更新操作事務(wù)滾回到開始時(shí)的狀態(tài)2.隱式事務(wù)當(dāng)用戶沒有顯式地定義事務(wù)時(shí)，DBMS按缺省規(guī)定自動(dòng)劃分事務(wù)。當(dāng)SQLServer首次執(zhí)行下列任何語句時(shí)，都會(huì)自動(dòng)啟動(dòng)一個(gè)事務(wù)：ALTERTABLE、INSERT、CREATE、OPEN、DELETE、REVOKE、DROP、SELECT、FETCH、TRUNCATETABLE、GRANT、UPDATE二、觸發(fā)器概述1.觸發(fā)器概念觸發(fā)器是一種特殊的存儲(chǔ)過程，它在執(zhí)行語言事件時(shí)自動(dòng)生效。觸發(fā)器是一段自動(dòng)執(zhí)行的程序，并不需要由用戶來直接調(diào)用；當(dāng)對表進(jìn)行INSERT、DELETE、UPDATE等操作時(shí)，自動(dòng)激活觸發(fā)器，并運(yùn)行其中的T-SQL語句。每個(gè)表可以建立多個(gè)觸發(fā)器，有插入觸發(fā)器、更新觸發(fā)器、刪除觸發(fā)器，分別對應(yīng)于INSERT、UPDATE和DELETE操作。也可以將多個(gè)操作定義為一個(gè)觸發(fā)器。2.觸發(fā)器優(yōu)點(diǎn)對表的修改合乎業(yè)務(wù)規(guī)則。如、有商品銷售時(shí)，銷售記錄添加后，庫存表中該商品的庫存量自動(dòng)減少。實(shí)現(xiàn)比CHECK約束更為復(fù)雜的約束。CHECK約束不允許引用其它表中的字段，而觸發(fā)器可以。實(shí)現(xiàn)參照完整性約束、數(shù)據(jù)庫中多表的級聯(lián)修改，可使用外鍵約束或觸發(fā)器。優(yōu)點(diǎn)3.觸發(fā)器的類別SQLServer2005包括兩大類觸發(fā)器：DML觸發(fā)器和DDL觸發(fā)器。（1）DML觸發(fā)器在數(shù)據(jù)庫中發(fā)生數(shù)據(jù)操作語言(DML)事件時(shí)將啟用。DML事件包括在指定表或視圖中修改數(shù)據(jù)的INSERT語句、UPDATE語句或DELETE語句。（2）DDL觸發(fā)器是SQLServer2005的新增功能。當(dāng)服務(wù)器或數(shù)據(jù)庫中發(fā)生數(shù)據(jù)定義語言(DDL)事件時(shí)將調(diào)用這些觸發(fā)器。4.INSERTED和DELETED臨時(shí)表觸發(fā)器被激活后，自動(dòng)創(chuàng)建這兩個(gè)臨時(shí)表。這兩個(gè)臨時(shí)表存放在內(nèi)存中，不存放在數(shù)據(jù)庫中。觸發(fā)器執(zhí)行完成后，兩個(gè)臨時(shí)表自動(dòng)刪除。INSERTED表:在執(zhí)行INSERT或UPDATE語句時(shí)，新的數(shù)據(jù)行被添加到數(shù)據(jù)表中，同時(shí)這些新的數(shù)據(jù)行被復(fù)制到inserted表中。DELETED表：在執(zhí)行delete或update語句時(shí)，從數(shù)據(jù)表中刪除舊的數(shù)據(jù)行，然后將這些舊的數(shù)據(jù)行存入deleted表中。Update操作相當(dāng)于先執(zhí)行delete操作，然后執(zhí)行insert操作。三、DML觸發(fā)器的應(yīng)用afterinsert觸發(fā)器當(dāng)記錄添加到表中后，該觸發(fā)器激活，創(chuàng)建inserted臨時(shí)表，添加到表中的新記錄行同時(shí)復(fù)制到inserted表。afterupdate觸發(fā)器當(dāng)表中記錄修改后，該觸發(fā)器激活，創(chuàng)建inserted和deleted臨時(shí)表，修改前的舊記錄行存入deleted表，修改后的新記錄行存入inserted表。

afterdelete觸發(fā)器當(dāng)表中記錄刪除后，該觸發(fā)器激活，創(chuàng)建deleted臨時(shí)表，刪除的記錄行存入deleted表。1.創(chuàng)建afterinsert觸發(fā)器創(chuàng)建觸發(fā)器CREATETRIGGER

觸發(fā)器名

ON表名

AFTERINSERT

AS SQL語句

創(chuàng)建afterinsert觸發(fā)器舉例例、當(dāng)商品供應(yīng)進(jìn)來后，該商品的庫存數(shù)量需自動(dòng)增加，創(chuàng)建觸發(fā)器實(shí)現(xiàn)，觸發(fā)器名為add_gy。ifexists(select*fromsysobjectswherename=‘a(chǎn)dd_gy'andtype='tr')droptriggeradd_gygocreatetriggeradd_gyon供應(yīng)afterinsertasdeclare@txvarchar(30),@slrealselect@tx=條形碼,@sl=供應(yīng)數(shù)量frominsertedupdate商品set庫存數(shù)量=庫存數(shù)量+@slwhere條形碼=@tx--運(yùn)行后查看供應(yīng)表下的觸發(fā)器add_gy已存在。--先運(yùn)行以下語句查看這兩表select*fromdbo.供應(yīng)select*fromdbo.商品--再運(yùn)行插入語句，檢驗(yàn)該觸發(fā)器的正確性insertinto供應(yīng)(供應(yīng)號(hào),條形碼,供應(yīng)數(shù)量,供應(yīng)時(shí)間,進(jìn)價(jià))values('20100123005','6930504300198',60,'2010-6-12',3.10)--再查看這兩表2.創(chuàng)建afterdelete觸發(fā)器CREATETRIGGER

觸發(fā)器名

ON表名

AFTERDELETE

AS SQL語句當(dāng)表中記錄刪除后，該觸發(fā)器激活，創(chuàng)建deleted臨時(shí)表，刪除的記錄行存入deleted表。刪除的記錄

DELETED表創(chuàng)建afterdelete觸發(fā)器舉例例、若某顧客回來退某商品，該如何實(shí)現(xiàn)這類操作？銷售記錄刪除后，商品表中該商品的庫存數(shù)量要自動(dòng)能修改。創(chuàng)建觸發(fā)器實(shí)現(xiàn)，觸發(fā)器名為del_xs。ifexists(select*fromsysobjectswherename='del_xs'andtype='tr')droptriggerdel_xsgocreatetriggerdel_xson進(jìn)行銷售

fordeleteas declare@txvarchar(30),@slreal select@tx=條形碼,@sl=銷售數(shù)量fromdeleted update商品set庫存數(shù)量=庫存數(shù)量+@slwhere@tx=條形碼

go--檢驗(yàn)該觸發(fā)器的正確性select*from進(jìn)行銷售select*from商品deletefrom進(jìn)行銷售where銷售號(hào)='101-106-44'and條形碼='6930504300198'select*from進(jìn)行銷售select*from商品過程：deletefrom進(jìn)行銷售where銷售號(hào)='101-106-44'and條形碼='6930504300198‘進(jìn)行銷售表有一個(gè)觸發(fā)器del_xs是一個(gè)afterdelete觸發(fā)器當(dāng)表中記錄刪除后，該觸發(fā)器激活，創(chuàng)建deleted臨時(shí)表，刪除的記錄行存入deleted表。select@tx=條形碼,@sl=銷售數(shù)量fromdeleted

結(jié)果是@tx=‘6930504300198‘，,@sl=10update商品set庫存數(shù)量=+@slwhere@tx=條形碼將商品表的條形碼為‘6930504300198‘的記錄的庫存數(shù)量增加103.創(chuàng)建afterupdate觸發(fā)器CREATETRIGGER觸發(fā)器名ON表名 AFTERUPDATE AS IFUPDATE(字段名)[AND|ORUPDATE(字段名)]

BEGIN SQL語句

END使用：IFUPDATE(字段名)[AND|ORUPDATE(字段名)]，則當(dāng)修改該字段的值時(shí)該觸發(fā)器觸發(fā)。如果不使用，則修改任何一個(gè)字段值時(shí)都會(huì)觸發(fā)。UPDATE觸發(fā)器該觸發(fā)器激活，創(chuàng)建inserted和deleted臨時(shí)表，修改前的舊記錄行存入deleted表，修改后的新記錄行存入inserted表。原有行DELETED表中，修改后的新行INSERTED表中。createtriggerxiu_sson進(jìn)行銷售

forupdate asif