SqlServer觸發(fā)器詳解
觸發(fā)器(trigger)是SQL server 提供給程序員和數(shù)據(jù)分析員來(lái)保證數(shù)據(jù)完整性的一種方法,它是與表事件相關(guān)的特殊的存儲(chǔ)過(guò)程,它的執(zhí)行不是由程序調(diào)用,也不是手工啟動(dòng),而是由事件來(lái)觸發(fā),比如當(dāng)對(duì)一個(gè)表進(jìn)行操作( insert,delete, update)時(shí)就會(huì)激活它執(zhí)行。
觸發(fā)器經(jīng)常用于加強(qiáng)數(shù)據(jù)的完整性約束和業(yè)務(wù)規(guī)則等。 觸發(fā)器可以從 DBA_TRIGGERS ,USER_TRIGGERS 數(shù)據(jù)字典中查到。SQL3的觸發(fā)器是一個(gè)能由系統(tǒng)自動(dòng)執(zhí)行對(duì)數(shù)據(jù)庫(kù)修改的語(yǔ)句。
觸發(fā)器可以查詢其他表,而且可以包含復(fù)雜的SQL語(yǔ)句。它們主要用于強(qiáng)制服從復(fù)雜的業(yè)務(wù)規(guī)則或要求。例如:您可以根據(jù)客戶當(dāng)前的帳戶狀態(tài),控制是否允許插入新訂單。
觸發(fā)器也可用于強(qiáng)制引用完整性,以便在多個(gè)表中添加、更新或刪除行時(shí),保留在這些表之間所定義的關(guān)系。然而,強(qiáng)制引用完整性的最好方法是在相關(guān)表中定義主鍵和外鍵約束。如果使用數(shù)據(jù)庫(kù)關(guān)系圖,則可以在表之間創(chuàng)建關(guān)系以自動(dòng)創(chuàng)建外鍵約束。
觸發(fā)器與存儲(chǔ)過(guò)程的唯一區(qū)別是觸發(fā)器不能執(zhí)行EXECUTE語(yǔ)句調(diào)用,而是在用戶執(zhí)行Transact-SQL語(yǔ)句時(shí)自動(dòng)觸發(fā)執(zhí)行。
查詢數(shù)據(jù)庫(kù)中所有觸發(fā)器:
select * from sysobjects where xtype='TR'
1、語(yǔ)法
create trigger [shema_name . ] trg_name on { table | view } [ with encryption ] { for | after | instead of } { insert , update , delete } as sql_statement
insert觸發(fā)器實(shí)例
create trigger test on al for insert as declare @id int,@uid int,@lid int,@result char select @id=id,@uid=uid,@lid=lid,@result=result from inserted if(@lid=4) begin update al set uid=99 where id=@id print 'lid=4時(shí)自動(dòng)修改用戶id為99' end
update觸發(fā)器實(shí)例
create trigger test_update on al for update as declare @oldid int,@olduid int,@oldlid int,@newid int,@newuid int,@newlid int select @oldid=id,@olduid=uid,@oldlid=lid from deleted; select @newid=id,@newuid=uid,@newlid=lid from inserted if(@newlid>@oldlid) begin print 'newlid>oldid' rollback tran; end else print '修改成功'
delete觸發(fā)器實(shí)例
create trigger test_delete on al for delete as declare @did int,@duid int,@dlid int select @did=id,@duid=uid,@dlid=lid from deleted if(exists(select * from list where @dlid=id)) begin print '無(wú)法刪除' rollback tran; end else print '刪除成功'
圖文介紹觸發(fā)器
數(shù)據(jù)庫(kù)運(yùn)行環(huán)境SqlServer2005
觸發(fā)器(trigger)是個(gè)特殊的存儲(chǔ)過(guò)程,它的執(zhí)行不是由程序調(diào)用,也不是手工啟動(dòng),而是由事件來(lái)觸發(fā),當(dāng)對(duì)一個(gè)表進(jìn)行操作( insert,delete, update)時(shí)就會(huì)激活它執(zhí)行,觸發(fā)器經(jīng)常用于加強(qiáng)數(shù)據(jù)的完整性約束和業(yè)務(wù)規(guī)則等。其實(shí)往簡(jiǎn)單了說(shuō),就是觸發(fā)器就是一個(gè)開(kāi)關(guān),負(fù)責(zé)燈的亮與滅,你動(dòng)了,它就亮了,就這個(gè)意思。
觸發(fā)器的分類
1 DML( 數(shù)據(jù)操縱語(yǔ)言 Data Manipulation Language)觸發(fā)器:是指觸發(fā)器在數(shù)據(jù)庫(kù)中發(fā)生DML事件時(shí)將啟用。DML事件即指在表或視圖中修改數(shù)據(jù)的insert、update、delete語(yǔ)句。
2 DDL(數(shù)據(jù)定義語(yǔ)言 Data Definition Language)觸發(fā)器:是指當(dāng)服務(wù)器或數(shù)據(jù)庫(kù)中發(fā)生(DDL事件時(shí)將啟用。DDL事件即指在表或索引中的create、alter、drop語(yǔ)句也。
3 登陸觸發(fā)器:是指當(dāng)用戶登錄SQL SERVER實(shí)例建立會(huì)話時(shí)觸發(fā)。
DML觸發(fā)器介紹
1 在SQL SERVER 2008中,DML觸發(fā)器的實(shí)現(xiàn)使用兩個(gè)邏輯表DELETED和INSERTED。這兩個(gè)表是建立在數(shù)據(jù)庫(kù)服務(wù)器的內(nèi)存中,我們只有只讀的權(quán)限。DELETED和INSERED表的結(jié)構(gòu)和觸發(fā)器所在的數(shù)據(jù)表的結(jié)構(gòu)是一樣的。當(dāng)觸發(fā)器執(zhí)行完成后,它們也就會(huì)被自動(dòng)刪除:INSERED表用于存放你在操件insert、update、delete語(yǔ)句后,更新的記錄。比如你插入一條數(shù)據(jù),那么就會(huì)把這條記錄插入到INSERTED表:DELETED表用于存放你在操作 insert、update、delete語(yǔ)句前,你創(chuàng)建觸發(fā)器表中數(shù)據(jù)庫(kù)。
2 觸發(fā)器可通過(guò)數(shù)據(jù)庫(kù)中的相關(guān)表實(shí)現(xiàn)級(jí)聯(lián)更改,可以強(qiáng)制比用CHECK約束定義的約束更為復(fù)雜的約束。與 CHECK 約束不同,觸發(fā)器可以引用其它表中的列,例如觸發(fā)器可以使用另一個(gè)表中的 SELECT 比較插入或更新的數(shù)據(jù),以及執(zhí)行其它操作。觸發(fā)器也可以根據(jù)數(shù)據(jù)修改前后的表狀態(tài),再行采取對(duì)策。一個(gè)表中的多個(gè)同類觸發(fā)器(INSERT、UPDATE 或 DELETE)允許采取多個(gè)不同的對(duì)策以響應(yīng)同一個(gè)修改語(yǔ)句。
3 與此同時(shí),雖然觸發(fā)器功能強(qiáng)大,輕松可靠地實(shí)現(xiàn)許多復(fù)雜的功能,為什么又要慎用?過(guò)多觸發(fā)器會(huì)造成數(shù)據(jù)庫(kù)及應(yīng)用程序的維護(hù)困難,同時(shí)對(duì)觸發(fā)器過(guò)分的依賴,勢(shì)必影響數(shù)據(jù)庫(kù)的結(jié)構(gòu),同時(shí)增加了維護(hù)的復(fù)雜程序。
觸發(fā)器步驟詳解
1 首先,我們來(lái)嘗試創(chuàng)建一個(gè)觸發(fā)器,要求就是在AddTable這個(gè)表上創(chuàng)建一個(gè)Update觸發(fā)器,語(yǔ)句為:
create trigger mytrigger on AddTable
for update
2 然后就是sql語(yǔ)句的部分了,主要是如果發(fā)生update以后,要求觸發(fā)器觸發(fā)一個(gè)什么操作。這里的意思就是如果出現(xiàn)update了,觸發(fā)器就會(huì)觸發(fā)輸出:the table was updated!---By 小豬也無(wú)奈。
3 接下來(lái)我們來(lái)將AddTable表中的數(shù)據(jù)執(zhí)行一個(gè)更改的操作:
4 執(zhí)行后,我們會(huì)發(fā)現(xiàn),觸發(fā)器被觸發(fā),輸出了我們?cè)O(shè)置好的文本:
5 那觸發(fā)器創(chuàng)建以后呢,它就正式開(kāi)始工作了,這時(shí)候我們需要更改觸發(fā)器的話,只需要將開(kāi)始的create創(chuàng)建變?yōu)閍lter,然后修改邏輯即可:
6 如果我們想查看某一個(gè)觸發(fā)器的內(nèi)容,直接運(yùn)行:exec sp_helptext [觸發(fā)器名]
7 如果我想查詢當(dāng)前數(shù)據(jù)庫(kù)中有多少觸發(fā)器,以方便我進(jìn)行數(shù)據(jù)庫(kù)維護(hù),只需要運(yùn)行:
select * from sysobjects where xtype='TR'
8 我們?nèi)绻枰P(guān)閉或者開(kāi)啟觸發(fā)器的話,只需要運(yùn)行:
disable trigger [觸發(fā)器名] on database --禁用觸發(fā)器
enable trigger [觸發(fā)器名] on database --開(kāi)啟觸發(fā)器
9 那觸發(fā)器的功能雖大,但是一旦觸發(fā),恢復(fù)起來(lái)就比較麻煩了,那我們就需要對(duì)數(shù)據(jù)進(jìn)行保護(hù),這里就需要用到rollback數(shù)據(jù)回滾~
10 第九步的意思就是查詢AddTable表,如果里面存在TableName=newTable的,數(shù)據(jù)就回滾,觸發(fā)器中止,那我們?cè)龠M(jìn)行一下測(cè)試,對(duì)AddTable表進(jìn)行更改,發(fā)現(xiàn),觸發(fā)update觸發(fā)器之后,因?yàn)橛袛?shù)據(jù)保護(hù),觸發(fā)器中止:
注意事項(xiàng)
禁用和開(kāi)啟觸發(fā)器都需要一定的權(quán)限,如果權(quán)限不夠是無(wú)法進(jìn)行操作的。
注意運(yùn)行后的錯(cuò)誤提示,對(duì)于糾正錯(cuò)誤是很有幫助的。
版權(quán)聲明:本站文章來(lái)源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有,歡迎引用、轉(zhuǎn)載,請(qǐng)保持原文完整并注明來(lái)源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站,禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像,否則將依法追究法律責(zé)任。本站部分內(nèi)容來(lái)源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來(lái),僅供學(xué)習(xí)參考,不代表本站立場(chǎng),如有內(nèi)容涉嫌侵權(quán),請(qǐng)聯(lián)系alex-e#qq.com處理。