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

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

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

BEGINTRANSACTION

BEGINTRANSACTIONSQL語句1SQL語句1

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

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

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

觸發(fā)器名

ON表名

AFTERINSERT

AS SQL語句

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

觸發(fā)器名

ON表名

AFTERDELETE

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

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

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

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

結果是@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(字段名)]，則當修改該字段的值時該觸發(fā)器觸發(fā)。如果不使用，則修改任何一個字段值時都會觸發(fā)。UPDATE觸發(fā)器該觸發(fā)器激活，創(chuàng)建inserted和deleted臨時表，修改前的舊記錄行存入deleted表，修改后的新記錄行存入inserted表。原有行DELETED表中，修改后的新行INSERTED表中。createtriggerxiu_sson進行銷售

forupdate asif