觸發(fā)器相關(guān)sql sever 編程

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

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

3、   -增量由統(tǒng)計得到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這里只打算講解四部分了，也就最簡單、最常用的四部分。1、觸發(fā)器。定義：何為觸發(fā)器？在SQL Server里面也就是對某一個表的一定的操作，觸發(fā)某種條件，從而執(zhí)行的一段程序。觸發(fā)器是一個特殊的存儲過程。常見的觸發(fā)器有三種：分別應用于Insert , Update , Delete 事件。(SQL Server 2000定義了新的觸發(fā)器，這里不提）我為什么要使用觸發(fā)器？比如，這么兩個表：Create Table Student( -學生表StudentID int primary key, -學號.)Create Table BorrowRec

7、ord( -學生借書記錄表BorrowRecord int identity(1,1), -流水號 StudentID int , -學號BorrowDate datetime, -借出時間ReturnDAte Datetime, -歸還時間.)用到的功能有:1.如果我更改了學生的學號,我希望他的借書記錄仍然與這個學生相關(guān)(也就是同時更改借書記錄表的學號);2.如果該學生已經(jīng)畢業(yè)，我希望刪除他的學號的同時，也刪除它的借書記錄。等等。這時候可以用到觸發(fā)器。對于1，創(chuàng)建一個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ā)器里面的兩個臨時的表：Deleted , Inserted 。注意Deleted 與Inserted分別表示觸發(fā)事件的表“舊的一條記錄”和“新的一條記錄”。一個Update 的過程可以看作為：生成新的記錄到Inserted表，復制舊的記錄到Deleted表，然后刪除Student記錄并寫入新紀錄。對于2，

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

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

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

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

13、語句都增加了判斷，只有在必須修改的時候才進行修改。其次，必須注意都多行修改的情況，所以必須都是連接inserted、deleted臨時表進行數(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 (    -這個判斷是防止無限遞歸的   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、( -必須有這個條件,防止重復插入    select 1 from T2 where id=i.id   )print '1I'    -報告觸發(fā)器執(zhí)行,只用于測試go-T2 Insert 觸發(fā)器Create Trigger tr_T2_Inserton T2for Insertasset nocount onif exists (    -這個判斷是防止無限遞歸的   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 ( -必須有這個條件,防止重復插入    select 1 from T1 where id=i.id   )print '2I'    -報告觸發(fā)器執(zhí)行

17、,只用于測試go-T1 Update 觸發(fā)器Create Trigger tr_T1_Updateon T1for updateasset nocount onif update (value)   -只有修改value才觸發(fā)begin      if exists (    -這個判斷是防止無限遞歸的     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 -這個條件保證只有真正修改了值才觸發(fā)endprint '1U'    -報告觸發(fā)器執(zhí)行,只用于測試go-T2 Update 觸發(fā)器Create Trigger tr_T2_Updateon T2for u

20、pdateasset nocount onif update (value)   -只有修改value才觸發(fā)begin      if exists (    -這個判斷是防止無限遞歸的       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 -這個條件保證只有真正修改了值才觸發(fā)endprint '2U'    -報告觸發(fā)器執(zhí)行,只用于測試go-T1 Delete 觸發(fā)器Create Trigger tr_T1_deleteon T1for deleteasset nocount onif exists (&

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

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

25、e       - - 1           1select * from T2id          value       - - 1           1-測試2insert T2

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

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

28、12           23           3-測試3update T1 set value=5where id=1-結(jié)果31U2U1U-這說明調(diào)用了兩個觸發(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-測試4update T1 set value=value+1where id>1-結(jié)果41U2U1U-這說明調(diào)用了兩個觸發(fā)器，其中1表的u

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