我的第一個觸發器練習

表ExA如下：
CREATE TABLE ExA (
 id int IDENTITY (1, 1) NOT NULL ,
 status bit NULL ,
 primary key (id)
)
select * from ExA
id          status
----------- ------
4           1
6           1

 

表ExB如下：
CREATE TABLE ExB (
 id int IDENTITY (1, 1) NOT NULL ,
 Aid int NULL ,
 mark varchar (50)  NULL ,
 primary key (id),
 foreign key (Aid) references ExA On Delete Cascade On Update Cascade
)
select * from ExB
id          Aid         mark                                              
----------- ----------- --------------------------------------------------
76          4           test1
77          4           test2
78          4           test3
79          4           test4
84          6           test1
85          6           test2
86          6           test3
87          6           test4

ExA.id與ExB.Aid是一對多的關係。

現在要求，當ExB中刪除記錄時，檢查表，若ExA的資料在ExB中沒有找到對應的資料，則將ExA.Status設定為0。

觸發器如下：
Alter Trigger trTestEx
On ExB
For Delete
As
Declare curDelete cursor for
 select Aid from Deleted group by Aid
Declare @intAid int
Open curDelete
Fetch Next from curDelete Into @intAid
while @@Fetch_Status=0
Begin
 if exists(select * from ExA where id=@intAid)
 Begin
  if not exists(select * from ExB where Aid=@intAid)
   Update ExA set status=0 where id=@intAid
 End
 Fetch Next from curDelete Into @intAid
End
Close curDelete
Deallocate curDelete
Go

Delete From ExB where Aid=4   --支援Delete From ExB where Aid in (4,6) 
結果如下：
select * from ExA
id          status
----------- ------
4           0
6           1
select * from ExB
id          Aid         mark                                              
----------- ----------- --------------------------------------------------
84          6           test1
85          6           test2
86          6           test3
87          6           test4