SQL Server觸發器的基本文法與作用

文章目錄

• 建立觸發的文法
• update觸發器樣本
• elete觸發器樣本

原文 http://www.cnblogs.com/xiongzhuang/archive/2013/05/13/3076943.html

什麼是觸發器？

• 觸發器是在對錶進行插入、更新或刪除操作時自動執行的預存程序
• 觸發器通常用於強制商務規則
• 觸發器是一種進階約束，可以定義比用CHECK 條件約束更為複雜的約束 ：可執行複雜的SQL語句（if/while/case）、可引用其它表中的列
• 觸發器定義在特定的表上，與表相關。
•  自動觸發執行
•  不能直接調用
•  是一個事務（可復原）

使用觸發器的好處

觸發器可通過資料庫中的相關表實現級聯更改，不過，通過級聯參考完整性約束可以更有效地執行這些更改。

觸發器可以強制比用CHECK約束定義的約束更為複雜的約束。與 CHECK 條件約束不同，觸發器可以引用

其它表中的列。例如，觸發器可以使用另一個表中的 SELECT 比較插入或更新的資料，以及執行其它操作，

如修改資料或顯示使用者定義錯誤資訊。觸發器也可以評估資料修改前後的表狀態，並根據其差異採取對策。

一個表中的多個同類觸發器（INSERT、UPDATE 或 DELETE）允許採取多個不同的對策以響應同一個修改語句。

觸發器的類型

• DELETE 觸發器
• INSERT 觸發器
• UPDATE 觸發器

建立觸發的文法

 

 

CREATE TRIGGER trigger_name ON table_name [WITH ENCRYPTION]  FOR [DELETE, INSERT, UPDATE] AS   T-SQL語句GO

 

注意：WITH ENCRYPTION表示加密觸發器定義的SQL文本 DELETE, INSERT, UPDATE指定觸發器的類型

insert 觸發器樣本 

/*建立insert觸發器，在上網記錄表recordInfo上建立插入觸發器*/create trigger tr_insert_recordInfoon recordInfofor insert as/*定義變數，用於臨時儲存插入的會員號、電腦編號和卡的編號*/declare @cardId char(10)declare @PCId intdeclare @CardNumber char(10)/*從inserted暫存資料表中擷取插入的記錄行的資訊，包括電腦的編號、卡的編號*/select @PCId=PCId,@cardId=CardId from inserted/*根據電腦編號修改電腦的使用狀態*/update PCInfo set PCUse=1 where PCId=@PCId/*根據卡的編號查詢會員號*/select @CardNumber=CardNumber from cardinfo where CardId=@cardid/*顯示上機成功的資訊*/print '上機成功！會員號是：'+@CardNumber+'機器號是：'+convert(char(10),@PCId)go----插入測試資料，會員號為的上機set nocount on      --不顯示sql語句影響的記錄行數declare @CardId int   ---聲明一個儲存卡的編號的變數---根據會員號查處卡的編號select @cardId=cardid from cardinfo where cardNumber='c001'  ---向recordInfo表中插入一條記錄資訊，卡的編號、電腦編號和上機時間 insert into recordInfo(cardId,PCId,beginTime) values(@cardId,1,getDate())----查看結果select * from recordInfoselect * from PCInfo

 update觸發器樣本 

---建立delete觸發器,在上網記錄表RecordInfo上建立刪除觸發器create  trigger tr_delete_recordInfoon recordInfofor deleteasif exists(select * from sysobjects where name='backRecordInfo')  ----如果backrecordInfo表存在，就添加記錄即可  insert into backRecordInfo select * from deletedelse  ----建立backRecordInfo表，從deleted中擷取被刪除的資料  select * into backRecordInfo from deletedprint'backRecordInfo表備份資料成功，備份表中的資料為：'   select * from backRecordInfogo-------關鍵代碼----------測試delete觸發器，刪除資料set nocount ondelete from recordInfo---查看結果print'記錄表中的資料為：'select * from recordInfo

 elete觸發器樣本 

-------關鍵代碼------create trigger tr_update_recordInfoon recordInfofor updateasdeclare @beforePCId intdeclare @afterPCId intselect @beforePCId =PCId from deletedselect @afterPCId=PCID from inserted---根據電腦編號修改使用狀態--------根據以前使用的電腦編號把電腦的使用狀態改為： update PCInfo set PCUse=0 where PCId=@beforePCId---根據現在使用的電腦編號把電腦的使用狀態改為: update PCInfo set PCUse=1 where PCId=@afterPCId----顯示電腦換機成功print'換機成功！從'+convert(varchar(10),@beforePCId)+'號電腦換到'+convert(varchar(10),@afterPCId)+'號電腦'go/*測試update觸發器，修改電腦編號*/--顯示更改前,記錄表中的資料print'更改前，記錄表中的資料'select * from recordInfo--顯示更改前，電腦表中的資料print'更改前，電腦表中的資料'select * from PCInfoset nocount on---把電腦號為1的改為2update recordInfo set PCId=2 where PCId=1  ---查看結果print'更改後，記錄表中的資料'select * from recordInfoprint'更改後，電腦表中的資料'select * from PCInfo

 instead of觸發器的使用instead of觸發器的使用範圍,instead of 觸發器可以同時在資料表和視圖中使用。通常在以下幾種情況下,建議使用instead of觸發器:資料庫裡的資料禁止修改:例如電信部門的通話記錄是不能修改的,一旦修改,則通話費用的計數將不準確。有可能要復原修改的SQL語句在視圖中使用觸發器用自己的方式去修改資料instead of觸發器樣本 

---建立update觸發器，在上網記錄表recordInfo上建立修改（列）觸發器create trigger tr_updateColum1_recordInfoon recordInfoinstead of insertas declare @cardbalance int  --聲明用於儲存使用者餘額的變數 declare @CardId int       --聲明用於儲存使用者卡的編號的變數 declare @PCId int         --聲明用於儲存電腦編號的變數---inserted暫存資料表中擷取插入的記錄行資訊，包括電腦編號、卡的編號select @cardId=cardId,@PCId=PCId from insertedselect @cardbalance=cardBalance from cardInfo where CardId=@CardIdprint'您的餘額為：'+convert(varchar(10),@cardBalance) ---列印餘額資訊if(@cardBalance<2)    ---判斷餘額多少，看能否正常上機print'餘額小於元，不能上機。請儘快儲值！'else----根據電腦的編號修改電腦的使用狀態更改為正在使用  update PCInfo set PCUse=1 where PCId=@PCId----向recordInfo表插入上機記錄insert into recordInfo(cardId,PCId,beginTime)values(@CardId,@PCId,getdate()) print'上機成功'-------關鍵代碼------set nocount ondeclare @cardId int  ---聲明一個儲存卡的編號的變數---根據會員號查出卡的編號select @cardId=cardId from cardInfo where cardNumber='c001'----向recordInfo表中插入一條記錄資訊，卡的編號、電腦的編號和上機時間insert into recordInfo(cardId,PCId,beginTime)values(@cardId,1,getdate())select * from recordInfoselect * from PCInfo