存儲過程與觸發(fā)器補充

1、數據庫設計與應用數據庫設計與應用浙江工業(yè)大學計算機學院浙江工業(yè)大學計算機學院一一存儲過程存儲過程二二觸發(fā)器觸發(fā)器2 觸發(fā)器觸發(fā)器2.1 創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器2.2 查看、修改和刪除觸發(fā)器查看、修改和刪除觸發(fā)器2.3 觸發(fā)器的應用觸發(fā)器的應用觸發(fā)器的概念觸發(fā)器的概念 觸發(fā)器是一種特殊類型的存儲過程。觸發(fā)器是一種特殊類型的存儲過程。 跟特定的操作相關聯(lián)跟特定的操作相關聯(lián) insert, update, delete 跟特定的表格相關聯(lián)跟特定的表格相關聯(lián) 觸發(fā)器觸發(fā)器通過通過事件觸發(fā)事件觸發(fā)而被執(zhí)行（不能被用戶調用），而而被執(zhí)行（不能被用戶調用），而存儲過程存儲過程則通過存儲過程名稱被則通過存儲過程

2、名稱被直接調用直接調用。 觸發(fā)器使每個站點可以在有數據修改時自動強制執(zhí)行其觸發(fā)器使每個站點可以在有數據修改時自動強制執(zhí)行其業(yè)務規(guī)則。觸發(fā)器可以用于業(yè)務規(guī)則。觸發(fā)器可以用于 SQL Server 約束、默認值約束、默認值和規(guī)則的完整性檢查。和規(guī)則的完整性檢查。為什么要用觸發(fā)器？為什么要用觸發(fā)器？ 跟蹤變化跟蹤變化 確保業(yè)務規(guī)則確保業(yè)務規(guī)則 自動記錄更改自動記錄更改 使視圖可更改使視圖可更改觸發(fā)器主要優(yōu)點觸發(fā)器主要優(yōu)點v 觸發(fā)器是自動的：當對表中的數據作了任何修改（比觸發(fā)器是自動的：當對表中的數據作了任何修改（比如手工輸入或者應用程序采取的操作）之后立即被激如手工輸入或者應用程序采取的操作）之后立

3、即被激活。活。 v 觸發(fā)器可以通過數據庫中的相關表進行層疊更改。觸發(fā)器可以通過數據庫中的相關表進行層疊更改。 v 觸發(fā)器可以強制限制，這些限制比用觸發(fā)器可以強制限制，這些限制比用 CHECK 約束所約束所定義的更復雜。定義的更復雜。 2.1 創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器 創(chuàng)建觸發(fā)器應該考慮以下幾個問題創(chuàng)建觸發(fā)器應該考慮以下幾個問題：CREATE TRIGGER 語句必須是批處理中的第一個語句。語句必須是批處理中的第一個語句。創(chuàng)建觸發(fā)器的權限默認分配給表的創(chuàng)建觸發(fā)器的權限默認分配給表的所有者所有者，且不能將該權，且不能將該權限轉給其他用戶。限轉給其他用戶。 觸發(fā)器為數據庫對象，其名稱必須遵循標識符的命名

4、規(guī)則。觸發(fā)器為數據庫對象，其名稱必須遵循標識符的命名規(guī)則。 雖然觸發(fā)器可以引用當前數據庫以外的對象，但只能在當雖然觸發(fā)器可以引用當前數據庫以外的對象，但只能在當前數據庫中創(chuàng)建觸發(fā)器，前數據庫中創(chuàng)建觸發(fā)器，且只能應用到一個表中且只能應用到一個表中。不能在臨時表或系統(tǒng)表上創(chuàng)建觸發(fā)器。不能在臨時表或系統(tǒng)表上創(chuàng)建觸發(fā)器。(1) 使用CREATE TRIGGER命令創(chuàng)建觸發(fā)器其語法形式如下：其語法形式如下：CREATE TRIGGER CREATE TRIGGER trigger_nametrigger_name -觸發(fā)器的名稱觸發(fā)器的名稱 ONtable|viewONtable|view -創(chuàng)建觸發(fā)器

5、的對象名稱（表或視圖）創(chuàng)建觸發(fā)器的對象名稱（表或視圖） WITH ENCRYPTION WITH ENCRYPTION -說明是否采用加密方法說明是否采用加密方法 FOR | AFTER | INSTEAD OF INSERT , UPDATE FOR | AFTER | INSTEAD OF INSERT , UPDATE , DELETE , DELETE NOT FOR REPLICATIONNOT FOR REPLICATION-說明當復制進程涉及觸發(fā)器定義的表時，該說明當復制進程涉及觸發(fā)器定義的表時，該觸發(fā)器不起作用觸發(fā)器不起作用 ASAS if update( if update(

6、列名列名) and | or update() and | or update(列名列名) ) sql_statementsql_statement 觸發(fā)器中使用的特殊表 inserted 邏輯表邏輯表：當向表中插入數據時，INSERT觸發(fā)器觸發(fā)執(zhí)行，新的記錄插入到觸發(fā)器表和inserted表中。 deleted 邏輯表邏輯表：用于保存已從表中刪除的記錄，當觸發(fā)一個DELETE觸發(fā)器時，被刪除的記錄存放到deleted邏輯表中。 修改記錄相當于刪除原有記錄，插入一條新記錄修改記錄相當于刪除原有記錄，插入一條新記錄。因此表中原記錄存放到deleted表中，而新記錄存放在inserted表中。 觸

7、發(fā)器中的SQL語句涉及的數據均來自上述兩張邏輯表中。這兩張表結構上類似于定義觸發(fā)器的表 在同一CREATE TRIGGER語句中，可以為多種操作（如 INSERT 和 UPDATE）定義相同的觸發(fā)器操作。 在觸發(fā)器內可以指定任意的 SET 語句，所選擇的 SET 選項在觸發(fā)器執(zhí)行期間有效，并在觸發(fā)器執(zhí)行完后恢復到以前的設置。 觸發(fā)器中不允許不允許包含以下 T-SQL 語句： CREATE DATABASE 、ALTER DATABASE 、LOAD DATABASE 、RESTORE DATABASE 、DROP DATABASE、LOAD LOG 、RESTORE LOG 、DISK INI

8、T、DISK RESIZE和RECONFIGURE 觸發(fā)器不能返回任何結果不能返回任何結果，為了阻止從觸發(fā)器返回結果，不要在觸發(fā)器定義中包含變量賦值語句。 使用觸發(fā)器的限制【例例1】對于XSCJ數據庫，如果在XS表中添加或更改數據，則將向客戶端顯示一條信息。/*使用帶有提示消息的觸發(fā)器*/USE XSCJUSE XSCJIF EXISTS (SELECT name FROM IF EXISTS (SELECT name FROM sysobjectssysobjects WHERE name = reminder AND type = TR)WHERE name = reminder AND

9、type = TR) DROP TRIGGER reminder DROP TRIGGER reminderGOGOCREATE TRIGGER reminder ON XSCREATE TRIGGER reminder ON XS FOR INSERT, UPDATE FOR INSERT, UPDATE AS AS RAISERROR (“ RAISERROR (“正在修改正在修改XSXS表表”, 16, 10), 16, 10)-返回用戶定義的錯誤信息并設系統(tǒng)標志，記錄發(fā)生錯誤返回用戶定義的錯誤信息并設系統(tǒng)標志，記錄發(fā)生錯誤GO(2) 使用企業(yè)管理器(企業(yè)分析器)創(chuàng)建觸發(fā)器2.2 2.2

10、 查看、修改和刪除觸發(fā)器查看、修改和刪除觸發(fā)器1. 1. 查看觸發(fā)器查看觸發(fā)器（）使用企業(yè)管理器查看觸發(fā)器信息（）使用企業(yè)管理器查看觸發(fā)器信息（）使用系統(tǒng)存儲過程查看觸發(fā)器（）使用系統(tǒng)存儲過程查看觸發(fā)器sp_helpsp_helpsp_helptextsp_helptext 觸發(fā)器名稱 sp_dependssp_depends 觸發(fā)器名稱語法形式語法形式sp_help：查看觸發(fā)器的一般信息，如觸發(fā)器的名稱、屬性、類型和創(chuàng)建時間。 sp_help 觸發(fā)器名稱 sp_helptext：用于查看觸發(fā)器的編寫文本 sp_helptext 觸發(fā)器名稱 sp_depends：用于查看指定觸發(fā)器所引用的表或

11、者指定的表涉及到的所有觸發(fā)器。 sp_depends 觸發(fā)器名稱 sp_depends 表名2. 修改觸發(fā)器修改觸發(fā)器 （）使用企業(yè)管理器修改觸發(fā)器正文（）使用企業(yè)管理器修改觸發(fā)器正文（）使用（）使用sp_renamesp_rename命令修改觸發(fā)器的名稱命令修改觸發(fā)器的名稱 sp_renamesp_rename oldname,newnameoldname,newname3. 3. 使用使用alter triggeralter trigger命令修改觸發(fā)器正文命令修改觸發(fā)器正文語法形式如下：語法形式如下：ALTER TRIGGER trigger_name ON(table|view) WI

12、THENCRYPTION FOR | AFTER | INSTEAD OF INSERT , UPDATE , DELETE NOT FOR REPLICATION AS if update(列名) and | or update(列名) sql_statement【例例2】修改XSCJ數據庫中在XS表上定義的觸發(fā)器reminder。USE XSCJALTER TRIGGER reminder ON XSFOR UPDATE AS RAISERROR (“執(zhí)行的操作是修改執(zhí)行的操作是修改”, 16, 10)GO (注注: 雙引號雙引號 與與 單引號單引號)3. 刪除觸發(fā)器刪除觸發(fā)器（）使用系統(tǒng)

13、命令DROP TRIGGER刪除指定的觸發(fā)器，其語法形式如下： DROP TRIGGER trigger ,.n DROP TRIGGER trigger ,.n （）刪除觸發(fā)器所在的表時，SQL Server將會自動刪除與該表相關的觸發(fā)器。（）在企業(yè)管理器中，用右鍵單擊要刪除的觸發(fā)器所在的表，從彈出的快捷菜單中選擇所有任務子菜單下的管理觸發(fā)器選項，則會出現(xiàn)觸發(fā)器屬性對話框。在名稱選項框中選擇要刪除的觸發(fā)器，單擊“刪除刪除”按鈕，即可刪除該觸發(fā)器。【例例3】 刪除觸發(fā)器reminder。USE XSCJIF EXISTS (SELECT name FROM sysobjects WHERE n

14、ame = reminder AND type = TR) DROP TRIGGER reminderGO如何讀取邏輯表？如何讀取邏輯表？USE skymemberIF EXISTS (SELECT name FROM sysobjects WHERE name = adding AND type = TR) DROP TRIGGER addingGOCREATE TRIGGER addingON m_loginFOR INSERTASBEGIN insert into m_login_bak select * from inserted ENDGO2.3 2.3 觸發(fā)器的應用觸發(fā)器的應用 1

15、. 使用使用INSERT觸發(fā)器觸發(fā)器 INSERT觸發(fā)器在對指定表進行數據插入操作時激活。 激活后將插入表中的數據復制并送入一個特殊的邏輯表（inserted表）中。 觸發(fā)器根據觸發(fā)器根據inserted表的值決定如何處理表的值決定如何處理?！纠?】在數據庫XSCJ中創(chuàng)建一觸發(fā)器，當向XS_KC表插入一記錄時，檢查該記錄的學號在XS表是否存在，檢查課程號在KC表中是否存在，若有一項為否，則不允許插入。USE XSCJIF EXISTS (SELECT name FROM sysobjects WHERE name = check_trig AND type = TR) DROP TRIGGE

16、R check_trigGOCREATE TRIGGER check_trigON XS_KCFOR INSERTASIf exists( SELECT * FROM inserted a WHERE a.學號學號 NOT IN (SELECT b.學號學號 FROM XS b) OR a.課程號課程號 NOT IN (SELECT c.課程號課程號 FROM KC c)BEGIN RAISERROR (違背數據的一致性違背數據的一致性., 16, 1) ROLLBACK TRANSACTION -回滾操作，即回滾到回滾操作，即回滾到insert語句執(zhí)行前的狀態(tài)語句執(zhí)行前的狀態(tài)END 例例55

17、：利用產品銷售數據庫：利用產品銷售數據庫CPXSCPXS，創(chuàng)建一個觸發(fā)器，創(chuàng)建一個觸發(fā)器tri_instri_ins_ _產品銷售產品銷售，確保，確保: : (1) (1) 插入數據時，產品編號的值必須為產品表中已有的值。插入數據時，產品編號的值必須為產品表中已有的值。 (2) (2) 確保插入的銷售日期小于或等于當前日期。確保插入的銷售日期小于或等于當前日期。注：銷售數據庫包含三張表，說明如下：注：銷售數據庫包含三張表，說明如下：產品（產品（產品編號產品編號，產品名稱，價格，庫存量）；，產品名稱，價格，庫存量）；銷售商（銷售商（客戶編號客戶編號，客戶名稱，地區(qū)，負責人，電話）；，客戶名稱，地

18、區(qū)，負責人，電話）；產品銷售（產品銷售（產品編號，客戶編號產品編號，客戶編號，銷售日期，數量，銷售額），銷售日期，數量，銷售額）CREATE TRIGGER tri_ins_產品銷售 ON 產品銷售 FOR INSERTAS IF (SELECT COUNT(*) FROM 產品 a, inserted i WHERE a.產品編號=i.產品編號) =0 BEGIN RAISERROR(不能插入不存在的產品號！,16,1) ROLLBACK TRANSACTION END ELSE IF (SELECT 銷售日期 FROM inserted i) getdate() BEGIN RAISERR

19、OR(不能插入遲于今天的銷售記錄！,16,1) ROLLBACK TRANSACTION END UPDATE觸發(fā)器在對指定表進行數據更新操作時激活。觸發(fā)器在對指定表進行數據更新操作時激活。激活后將被更新的原數據復制到激活后將被更新的原數據復制到deleted表表，再將更新，再將更新后的新數據送入后的新數據送入inserted表表中。中。觸發(fā)器對觸發(fā)器對deleted和和inserted表進行檢查，并決定如何表進行檢查，并決定如何處理。處理。2. 2. 使用使用UPDATEUPDATE觸發(fā)器觸發(fā)器 例例66：創(chuàng)建一個觸發(fā)器：創(chuàng)建一個觸發(fā)器tri_updtri_upd_ _產品銷售產品銷售 (1) (1) 當在產品銷售表中更新數據時，維護其參當在產品銷售表中更新數據時，維護其參照完整性，即若照完整性，即若更新產品編號和客戶編號字段更新產品編號和客戶編號字段時，必須確保更新后的值包含在產品表和銷售時，必須確保更新后的值包含在產品表和銷售表中。表中。Create trigger tri_upd_產品銷售 on 產品銷售 for update as if update(產品編號) if (select count(*) from 產品 a inner join