SQLSever中的觸發器基本語法與作用

什么是觸發器？

觸發器是在對表進行插入、更新或刪除操作時自動執行的存儲過程。觸發器對表進行插入、更新、刪除的時候會自動執行的特殊存儲過程。觸發器一般用在check約束更加復雜的約束上面。觸發器和普通的存儲過程的區別是：觸發器是當對某一個表進行操作。諸如：update、insert、delete這些操作的時候，系統會自動調用執行該表上對應的觸發器。SQL Server 2005中觸發器可以分為兩類：DML觸發器和DDL觸發器，其中DDL觸發器它們會影響多種數據定義語言語句而激發，這些語句有create、alter、drop語句。

SQL Server為每個觸發器都創建了兩個專用表﹕Inserted表和Deleted表。

觸發器通常用于強制業務規則

觸發器是一種高級約束，可以定義比用CHECK 約束更為復雜的約束：可執行復雜的SQL語句（if/while/case）、可引用其它

表中的列

觸發器定義在特定的表上，與表相關。

自動觸發執行

不能直接調用

是一個事務（可回滾）使用觸發器的好處觸發器可通過數據庫中的相關表實現級聯更改，不過，通過級聯引用完整性約束可以更有效地執行這些更改。觸發器可以強制比用CHECK約束定義的約束更為復雜的約束。與 CHECK 約束不同，觸發器可以引用其它表中的列。例如，觸發器可以使用另一個表中的 SELECT 比較插入或更新的數據，以及執行其它操作，如修改數據或顯示用戶定義錯誤信息。觸發器也可以評估數據修改前后的表狀態，并根據其差異采取對策。一個表中的多個同類觸發器（INSERT、UPDATE 或 DELETE）允許采取多個不同的對策以響應同一個修改語句。

DML觸發器分為：

1、 after觸發器（之后觸發）

a、 insert觸發器

b、 update觸發器

c、 delete觸發器

UPDATE 觸發器創建觸發的語法

CREATE TRIGGER trigger_nameON table_name[WITH ENCRYPTION]FOR [DELETE, INSERT, UPDATE]AST-SQL語句GO

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

/*創建insert觸發器，在上網記錄表recordInfo上創建插入觸發器*/create trigger tr_insert_recordInfoon recordInfofor insertas/*定義變量，用于臨時存儲插入的會員號、電腦編號和卡的編號*/declare @cardId char()declare @PCId intdeclare @CardNumber char()/*從inserted臨時表中獲取插入的記錄行的信息，包括電腦的編號、卡的編號*/select @PCId=PCId,@cardId=CardId from inserted/*根據電腦編號修改電腦的使用狀態*/update PCInfo set PCUse= where PCId=@PCId/*根據卡的編號查詢會員號*/select @CardNumber=CardNumber from cardinfo where CardId=@cardid/*顯示上機成功的信息*/print '上機成功！會員號是：'+@CardNumber+'機器號是：'+convert(char(),@PCId)go----插入測試數據，會員號為的上機set nocount on --不顯示sql語句影響的記錄行數declare @CardId int ---聲明一個存儲卡的編號的變量---根據會員號查處卡的編號select @cardId=cardid from cardinfo where cardNumber='c' ---向recordInfo表中插入一條記錄信息，卡的編號、電腦編號和上機時間insert into recordInfo(cardId,PCId,beginTime) values(@cardId,,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

delete觸發器示例

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

以上所述是小編給大家介紹的SQLSever中的觸發器基本語法與作用的相關內容，希望對大家有所幫助！