1、觸發(fā)器相關(guān)sql sever 編程一個(gè)典型的應(yīng)用有兩個(gè)表:user表(userid,username,userpwd,sex,departid) /userid主鍵department表(departid,departname,membercount) /departid主鍵兩表建立了外鍵約束membercount是部門人數(shù)，在插入一個(gè)Userde的時(shí)候相應(yīng)部門的membercount需要加1，刪除的時(shí)候需要減1，修改departid的時(shí)候需要在改前部門減1，改后部門加1。想用觸發(fā)器實(shí)現(xiàn)在更新user表時(shí)自動(dòng)更新department表的membercount。-觸發(fā)器代碼create trig

2、ger tr_useron userfor insert,update,deleteasset nocount onupdate aset membercount=a.membercount+b.membercount           -b.membercount是這次操作的增量，可能為負(fù)數(shù)from department a,(select departid,sum(membercount) as membercount      

3、   -增量由統(tǒng)計(jì)得到from (select departid,1 as membercount                        -插入或者改后的部門+1from inserted        union allselect departid,-1 as mem

4、bercount                       -刪除或者改前的部門-1from deleted) as tgroup by departidhaving sum(membercount)<>0             &#

5、160;                -只取<>0的數(shù)據(jù)減少更改的記錄數(shù)) as bwhere a.departid=b.departid                         

6、;    -連接條件go這里只打算講解四部分了，也就最簡(jiǎn)單、最常用的四部分。1、觸發(fā)器。定義：何為觸發(fā)器？在SQL Server里面也就是對(duì)某一個(gè)表的一定的操作，觸發(fā)某種條件，從而執(zhí)行的一段程序。觸發(fā)器是一個(gè)特殊的存儲(chǔ)過(guò)程。常見的觸發(fā)器有三種：分別應(yīng)用于Insert , Update , Delete 事件。(SQL Server 2000定義了新的觸發(fā)器，這里不提）我為什么要使用觸發(fā)器？比如，這么兩個(gè)表：Create Table Student( -學(xué)生表StudentID int primary key, -學(xué)號(hào).)Create Table BorrowRec

7、ord( -學(xué)生借書記錄表BorrowRecord int identity(1,1), -流水號(hào) StudentID int , -學(xué)號(hào)BorrowDate datetime, -借出時(shí)間ReturnDAte Datetime, -歸還時(shí)間.)用到的功能有:1.如果我更改了學(xué)生的學(xué)號(hào),我希望他的借書記錄仍然與這個(gè)學(xué)生相關(guān)(也就是同時(shí)更改借書記錄表的學(xué)號(hào));2.如果該學(xué)生已經(jīng)畢業(yè)，我希望刪除他的學(xué)號(hào)的同時(shí)，也刪除它的借書記錄。等等。這時(shí)候可以用到觸發(fā)器。對(duì)于1，創(chuàng)建一個(gè)Update觸發(fā)器：Create Trigger truStudentOn Studentfor UpdateAsif Upd

8、ate(StudentID)beginUpdate BorrowRecord Set StudentID=i.StudentIDFrom BorrowRecord br , Deleted d ,Inserted i Where br.StudentID=d.StudentIDend 理解觸發(fā)器里面的兩個(gè)臨時(shí)的表：Deleted , Inserted 。注意Deleted 與Inserted分別表示觸發(fā)事件的表“舊的一條記錄”和“新的一條記錄”。一個(gè)Update 的過(guò)程可以看作為：生成新的記錄到Inserted表，復(fù)制舊的記錄到Deleted表，然后刪除Student記錄并寫入新紀(jì)錄。對(duì)于2，

9、創(chuàng)建一個(gè)Delete觸發(fā)器Create trigger trdStudentOn Studentfor DeleteAsDelete BorrowRecord From BorrowRecord br , Delted dWhere br.StudentID=d.StudentID從這兩個(gè)例子我們可以看到了觸發(fā)器的關(guān)鍵：A.2個(gè)臨時(shí)的表；B.觸發(fā)機(jī)制。這里我們只講解最簡(jiǎn)單的觸發(fā)器。復(fù)雜的容后說(shuō)明。事實(shí)上，我不鼓勵(lì)使用觸發(fā)器。觸發(fā)器的初始設(shè)計(jì)思想，已經(jīng)被“級(jí)聯(lián)”所替代.觸發(fā)器一個(gè)重要的作用是在修改一個(gè)表的數(shù)據(jù)的同時(shí)可以修改另一個(gè)表的數(shù)據(jù)，這里所說(shuō)的修改是指包括插入、更新和刪除操作。但是，在編寫觸

10、發(fā)器代碼的時(shí)候，需要考慮各種可能的情況，使得觸發(fā)器代碼變得健壯，可以適應(yīng)未來(lái)的各種變化。        一般來(lái)說(shuō)，需要考慮以下情況：1、一次修改多條記錄的情況，無(wú)論是插入、刪除還是修改，都有可能一次操作多條記錄，所以觸發(fā)器中應(yīng)該避免使用變量來(lái)取得inserted、deleted臨時(shí)表的字段值,因?yàn)檫@樣取值很可能只取得一條記錄的字段值，造成其他記錄未處理，而且這種錯(cuò)誤在語(yǔ)法上沒有任何報(bào)錯(cuò)，在大部分應(yīng)用的時(shí)候（大部分應(yīng)用一次只處理一條記錄）不容易發(fā)現(xiàn)錯(cuò)誤，而一旦發(fā)生錯(cuò)誤的時(shí)候，又很難找出錯(cuò)誤所在，因?yàn)楹芏鄷r(shí)候我們不會(huì)首先懷疑觸發(fā)

11、器的問(wèn)題。所以個(gè)人更愿意把這類問(wèn)題作為第一個(gè)問(wèn)題提出。2、低效的語(yǔ)句，在觸發(fā)器中，更應(yīng)該注意語(yǔ)句的效率，因?yàn)槊看螌?duì)這個(gè)表的操作，都會(huì)調(diào)用這個(gè)觸發(fā)器。效率問(wèn)題首先是需要縮小數(shù)據(jù)修改的范圍，充分利用inserted、deleted臨時(shí)表連接相關(guān)表來(lái)減少數(shù)據(jù)操作的記錄數(shù)。其次是觸發(fā)器操作的的表最好都設(shè)置主鍵和聚集索引，既保證數(shù)據(jù)的一致性也可以提高代碼的效率。3、遞歸和嵌套觸發(fā)器，sql server安裝的時(shí)候，默認(rèn)是允許嵌套觸發(fā)器和不允許直接遞歸觸發(fā)器，有些應(yīng)用可以關(guān)閉嵌套觸發(fā)器選項(xiàng)來(lái)保證不會(huì)發(fā)生遞歸,但是大部分情況是需要打開嵌套觸發(fā)器選項(xiàng)來(lái)保證數(shù)據(jù)的一致性的,關(guān)閉嵌套觸發(fā)器選項(xiàng)是需要認(rèn)真認(rèn)證后才能

12、作出的決定。在打開嵌套觸發(fā)器選項(xiàng)的情況下，需要很好的利用代碼防止無(wú)限遞歸觸發(fā)器的發(fā)生。4、必須注重觸發(fā)器的測(cè)試，要在各種情況下（空表、有一條數(shù)據(jù)、有多條數(shù)據(jù)），各種可能的操作（插入、刪除、修改、一條記錄、多條記錄等）都要測(cè)試。          下面以一個(gè)例子來(lái)說(shuō)明編寫觸發(fā)器需要注意的地方，這是一個(gè)保持兩個(gè)結(jié)構(gòu)相同的表的數(shù)據(jù)一致的處罰其解決方案，要求任何一個(gè)表的改動(dòng)在另一個(gè)表度需要作相應(yīng)改動(dòng)。首先，這個(gè)事例很容易引起觸發(fā)器的間接遞歸，間接遞歸是應(yīng)該允許的，但是必須防止無(wú)限間接遞歸的情況發(fā)生。所以在觸發(fā)器中操作數(shù)據(jù)的

13、語(yǔ)句都增加了判斷，只有在必須修改的時(shí)候才進(jìn)行修改。其次，必須注意都多行修改的情況，所以必須都是連接inserted、deleted臨時(shí)表進(jìn)行數(shù)據(jù)操作。下面是代碼和注釋-建表create table T1(id int not null PRIMARY KEY, -這里必須有主鍵value int)gocreate table T2(    -這里必須有主鍵id int not null PRIMARY KEY,value int)go-觸發(fā)器代碼-T1 Insert 觸發(fā)器Create Trigger tr_T1_Inserton T1for Insertasse

14、t nocount on   if exists (    -這個(gè)判斷是防止無(wú)限遞歸的   select 1 from inserted i   where not exists (    select 1 from T2 where id=i.id    )   )insert T2   select * from inserted i   where not exists

15、( -必須有這個(gè)條件,防止重復(fù)插入    select 1 from T2 where id=i.id   )print '1I'    -報(bào)告觸發(fā)器執(zhí)行,只用于測(cè)試go-T2 Insert 觸發(fā)器Create Trigger tr_T2_Inserton T2for Insertasset nocount onif exists (    -這個(gè)判斷是防止無(wú)限遞歸的   select 1 from inserted i   wh

16、ere not exists (    select 1 from T1 where id=i.id    )   )insert T1   select * from inserted i   where not exists ( -必須有這個(gè)條件,防止重復(fù)插入    select 1 from T1 where id=i.id   )print '2I'    -報(bào)告觸發(fā)器執(zhí)行

17、,只用于測(cè)試go-T1 Update 觸發(fā)器Create Trigger tr_T1_Updateon T1for updateasset nocount onif update (value)   -只有修改value才觸發(fā)begin      if exists (    -這個(gè)判斷是防止無(wú)限遞歸的     select 1       from T2,Inserted i,deleted d 

18、0;     where i.id=d.id       and i.id=T2.id       and i.value<>d.value   )       update T2          set value=i.Value       from

19、T2,Inserted i,deleted d       where i.id=d.id       and i.id=T2.id       and i.value<>d.value -這個(gè)條件保證只有真正修改了值才觸發(fā)endprint '1U'    -報(bào)告觸發(fā)器執(zhí)行,只用于測(cè)試go-T2 Update 觸發(fā)器Create Trigger tr_T2_Updateon T2for u

20、pdateasset nocount onif update (value)   -只有修改value才觸發(fā)begin      if exists (    -這個(gè)判斷是防止無(wú)限遞歸的       select 1       from T1,Inserted i,deleted d       where i.id=d.id   &#

21、160;   and i.id=T1.id       and i.value<>d.value      )       update T1           set value=i.Value       from T1,Inserted i,deleted d 

22、0;     where i.id=d.id       and i.id=T1.id       and i.value<>d.value -這個(gè)條件保證只有真正修改了值才觸發(fā)endprint '2U'    -報(bào)告觸發(fā)器執(zhí)行,只用于測(cè)試go-T1 Delete 觸發(fā)器Create Trigger tr_T1_deleteon T1for deleteasset nocount onif exists (&

23、#160;   -這個(gè)判斷是防止無(wú)限遞歸的   select 1 from T2,deleted d   where T2.id=d.Id)delete T2from deleted dwhere T2.id=d.Idprint '1D'    -報(bào)告觸發(fā)器執(zhí)行,只用于測(cè)試go-T2 Delete 觸發(fā)器Create Trigger tr_T2_deleteon T2for deleteasset nocount onif exists (    -這個(gè)判斷是

24、防止無(wú)限遞歸的   select 1 from T1,deleted d   where T1.id=d.Id)delete T1from deleted dwhere T1.id=d.Idprint '2D'    -報(bào)告觸發(fā)器執(zhí)行,只用于測(cè)試go-測(cè)試-測(cè)試1insert T1 values(1,1)-結(jié)果12I1I-這說(shuō)明調(diào)用了兩個(gè)觸發(fā)器-以下查看數(shù)據(jù)select * from T1id          valu

25、e       - - 1           1select * from T2id          value       - - 1           1-測(cè)試2insert T2

26、 select 2,2 union all select 3,3        -一次插入兩條記錄-結(jié)果21I2I-這說(shuō)明調(diào)用了兩個(gè)觸發(fā)器-以下查看數(shù)據(jù)select * from T1id          value       - - 1           12 

27、          23           3select * from T2id          value       - - 1          

28、12           23           3-測(cè)試3update T1 set value=5where id=1-結(jié)果31U2U1U-這說(shuō)明調(diào)用了兩個(gè)觸發(fā)器，其中1表的update觸發(fā)器調(diào)用了兩次-以下查看數(shù)據(jù)select * from T1id          value  

29、;     - - 1           52           23           3select * from T2id          value

30、60;      - - 1           52           23           3-測(cè)試4update T1 set value=value+1where id>1-結(jié)果41U2U1U-這說(shuō)明調(diào)用了兩個(gè)觸發(fā)器，其中1表的u

31、pdate觸發(fā)器調(diào)用了兩次-以下查看數(shù)據(jù)select * from T1id          value       - - 1           52           33           4select * from T2id          value       - - 1           52