SQLServer触发器增删改INSERT,UPDATE,DELETE

最新推荐文章于 2024-08-25 17:55:11 发布

Mick_小马哥

最新推荐文章于 2024-08-25 17:55:11 发布

阅读量5.6k

点赞数 1

分类专栏： SQLServer 文章标签： SQLServer 触发器

本文链接：https://blog.csdn.net/qq_22103321/article/details/86689342

版权

SQLServer 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

CREATE TRIGGER trig_User_IUD
   ON   dbo.QR_User
   AFTER INSERT,UPDATE,DELETE
AS 
Declare @number nvarchar(20)
if exists(select 1 from inserted)
BEGIN	
	Select @number=propertyId from inserted
	if (select COUNT(U.KeyId) from dbo.QR_User U,dbo.QR_Property P where P.number=@number and U.sh_status=0 and U.propertyId=@number)>0
		update QR_Property set sub_status=1 where number=@number
	else if (select COUNT(I.KeyId) from dbo.QR_Inspection I,dbo.QR_Property P where P.number=@number and I.sh_status=0 and I.propertyId=P.KeyId)>0
		update QR_Property set sub_status=1	where number=@number
	else if (select COUNT(KeyId) from dbo.QR_Property where sh_status=0 and number=@number)>0
		update QR_Property set sub_status=1	where number=@number
	else
		update QR_Property set sub_status=0	where number=@number
END
else if exists (select 1 from deleted)
BEGIN
	Select @number=propertyId from deleted
	if (select COUNT(U.KeyId) from dbo.QR_User U,dbo.QR_Property P where P.number=@number and U.sh_status=0 and U.propertyId=@number)>0
		update QR_Property set sub_status=1 where number=@number
	else if (select COUNT(I.KeyId) from dbo.QR_Inspection I,dbo.QR_Property P where P.number=@number and I.sh_status=0 and I.propertyId=P.KeyId)>0
		update QR_Property set sub_status=1	where number=@number
	else if (select COUNT(KeyId) from dbo.QR_Property where sh_status=0 and number=@number)>0
		update QR_Property set sub_status=1	where number=@number
	else
		update QR_Property set sub_status=0	where number=@number
END