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

最新推荐文章于 2025-03-16 10:49:49 发布

原创最新推荐文章于 2025-03-16 10:49:49 发布 · 5.8k 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#SQLServer #触发器

SQLServer 专栏收录该内容

12 篇文章

订阅专栏

本文详细解析了一个SQL触发器的实现逻辑，该触发器在QR_User表上操作后（插入、更新或删除），根据相关联的QR_Property表中记录的状态，更新其sub_status字段。通过复杂的条件判断，确保了属性状态的准确性。

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