我想写个触发器,两个表,一张“工艺路线表”,包括有“设备”和“设备功率”两个属性。另一张表是“设备表”,有“设备编号”和“设备功率”属性,工艺路线的“设备”对应设备表的“设备编号”。我想当更新“工艺路线表”中的“设备”时,自动将“设备表”中对应设备的“设备功率”写入“工艺路线表”中的“设备功率”。我只会写当更新一条记录时的触发器,当同时更新多条记录时就不会写了。下面是我写的代码,t_RoutingOper是工艺路线表,t_Resource是设备表
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER TRIGGER [dbo].[t_RoutingOper_Update] ON [dbo].[t_RoutingOper]
FOR UPDATE
AS
IF UPDATE(FDeviceID) and exists(select 1 from Inserted where FDeviceID <>'0')
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON; -- Insert statements for trigger here
update t_RoutingOper set FDevicePower2 =(select FDevicePower from t_Resource
where t_Resource.FInterID=(select FDeviceID from Inserted))
where t_RoutingOper.FEntryID=(select FEntryID from Inserted)
and t_RoutingOper.FInterID=(select FInterID from Inserted)
END
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER TRIGGER [dbo].[t_RoutingOper_Update] ON [dbo].[t_RoutingOper]
FOR UPDATE
AS
IF UPDATE(FDeviceID) and exists(select 1 from Inserted where FDeviceID <>'0')
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON; -- Insert statements for trigger here
update t_RoutingOper set FDevicePower2 =(select FDevicePower from t_Resource
where t_Resource.FInterID=(select FDeviceID from Inserted))
where t_RoutingOper.FEntryID=(select FEntryID from Inserted)
and t_RoutingOper.FInterID=(select FInterID from Inserted)
END
where t.FInterID=i.FDeviceID
and a.FEntryID=i.FEntryID
and a.FInterID=i.FInterID
update t_RoutingOper set FDeviceID2 ='1058' where FinterID='1269'
但FinterID='1269'的记录是有多条的,即Inserted表中有多条记录,上述语句就不能
实现所需功能了