CREATE TRIGGER StateChanged ON [dbo].[SubSheetInfo] FOR UPDATE AS BEGIN IF UPDATE(状态) UPDATE [dbo].[MainSheetInfo] SET 状态 = '已结' WHERE NOT EXISTS (SELECT * FROM [dbo].[SubSheetInfo] WHERE [dbo].[SubSheetInfo].[主运单号]= [dbo].[MainSheetInfo].[主运单号] AND [dbo].[SubSheetInfo] .[状态] <> '已结' END )
CREATE TRIGGER StateChanged ON [dbo].[SubSheetInfo] FOR UPDATE AS BEGIN IF UPDATE(状态) UPDATE [dbo].[MainSheetInfo] SET 状态 = '已结' WHERE NOT EXISTS (SELECT * FROM [dbo].[SubSheetInfo] WHERE [dbo].[SubSheetInfo].[主运单号]= [dbo].[MainSheetInfo].[主运单号] AND [dbo].[SubSheetInfo] .[状态] <> '已结' ) END
SET @MainID=(select 主运单号 from inserted) 不好意思,写错了。
但实际上楼主这样,只能在更新一条时是正确的。如果UPDATE多条记录时,会出错。因为触发器只会触发一次。所以应该改成 CREATE TRIGGER StateChanged ON [dbo].[SubSheetInfo] FOR UPDATE AS IF UPDATE(状态) UPDATE [dbo].[MainSheetInfo] SET 状态 = '已结' WHERE [dbo].[MainSheetInfo].[主运单号] in (select 主运单号 from inserted) AND '已结'= ALL (SELECT 状态 FROM [dbo].[SubSheetInfo] WHERE [dbo].[SubSheetInfo].[主运单号] in (select 主运单号 from inserted))
SET @MainID=[dbo].[SubSheetInfo].[主运单号] 你是想做什么?有没有考虑同时更新了很多记录?
FOR UPDATE
AS
BEGIN
IF UPDATE(状态)
UPDATE [dbo].[MainSheetInfo]
SET 状态 = '已结'
WHERE NOT EXISTS (SELECT * FROM [dbo].[SubSheetInfo] WHERE [dbo].[SubSheetInfo].[主运单号]= [dbo].[MainSheetInfo].[主运单号] AND [dbo].[SubSheetInfo] .[状态] <> '已结'
END
)
FOR UPDATE
AS
BEGIN
IF UPDATE(状态)
UPDATE [dbo].[MainSheetInfo]
SET 状态 = '已结'
WHERE NOT EXISTS (SELECT * FROM [dbo].[SubSheetInfo] WHERE [dbo].[SubSheetInfo].[主运单号]= [dbo].[MainSheetInfo].[主运单号] AND [dbo].[SubSheetInfo] .[状态] <> '已结' )
END
不好意思,写错了。
CREATE TRIGGER StateChanged ON [dbo].[SubSheetInfo]
FOR UPDATE
AS
IF UPDATE(状态)
UPDATE [dbo].[MainSheetInfo]
SET 状态 = '已结'
WHERE [dbo].[MainSheetInfo].[主运单号] in (select 主运单号 from inserted)
AND '已结'= ALL (SELECT 状态 FROM [dbo].[SubSheetInfo] WHERE [dbo].[SubSheetInfo].[主运单号] in (select 主运单号 from inserted))