表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