SQL2005 建立触发器时报错“无法绑定由多个部分组成的标识符"inserted.字段名"”

CREATE TRIGGER TR_2 ON BeginTran for insert
as
begin

update MotorInfo
set MotorInfo.CCCS = MotorInfo.CCCS +1
where MotorInfo.MotorNo = inserted.MotorNum

end

错误原因：无法绑定由多个部分组成的标识符"inserted.MotorNum"

解决方法一:

将inserted.MotorNum改为 select MotorNum from inserted

方法二: 给inserted起个别名

CREATE TRIGGER TR_2 ON BeginTran for insert
as
begin

update MotorInfo
set MotorInfo.CCCS = MotorInfo.CCCS +1

from inserted i
where MotorInfo.MotorNo = i.MotorNum

end

 

 

 

实例：

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author:  <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER TRIGGER [MeasValJFTriggerInsert]
   ON  [dbo].[SPC_Product_MeasValJF]
   --for INSERT
   FOR INSERT,UPDATE,DELETE
AS
BEGIN
    IF EXISTS(SELECT 1 FROM inserted) AND NOT EXISTS(SELECT 1 FROM deleted)
      BEGIN
          --PRINT('INSERTING')
          insert into SpcB2B.dbo.BtoB_SPCTemp
          select inserted.listNo,inserted.ProductKey,inserted.MeasKey,inserted.MeasNo,
          dbo.SPC_Product_Meas.MeasName,inserted.MeasDate,inserted.field1,inserted.field2,
          inserted.X1,inserted.X2,inserted.X3,inserted.X4,inserted.X5,
          inserted.X6,inserted.X7,inserted.X8,inserted.X9,inserted.X10,
          inserted.X11,inserted.X12,inserted.X13,inserted.X14,inserted.X15,
          inserted.X16,inserted.X17,inserted.X18,inserted.X19,inserted.X20,
          inserted.MeasQuality,inserted.MeasReason,inserted.MeasSolution,inserted.Note
          from inserted
          left outer join dbo.SPC_Product_Meas on dbo.SPC_Product_Meas.MeasKey = inserted.MeasKey and dbo.SPC_Product_Meas.ProductKey = inserted.ProductKey
      END
    IF EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
      BEGIN    
          --PRINT('UPDATING')
          update SpcB2B.dbo.BtoB_SPCTemp
          set X1=(select inserted.X1 from inserted where inserted.listno=SpcB2B.dbo.BtoB_SPCTemp.listno),
              X2=(select inserted.X2 from inserted where inserted.listno=SpcB2B.dbo.BtoB_SPCTemp.listno),
              X3=(select inserted.X3 from inserted where inserted.listno=SpcB2B.dbo.BtoB_SPCTemp.listno),
              X4=(select inserted.X4 from inserted where inserted.listno=SpcB2B.dbo.BtoB_SPCTemp.listno),
              X5=(select inserted.X5 from inserted where inserted.listno=SpcB2B.dbo.BtoB_SPCTemp.listno),
              X6=(select inserted.X6 from inserted where inserted.listno=SpcB2B.dbo.BtoB_SPCTemp.listno),
              X7=(select inserted.X7 from inserted where inserted.listno=SpcB2B.dbo.BtoB_SPCTemp.listno),
              X8=(select inserted.X8 from inserted where inserted.listno=SpcB2B.dbo.BtoB_SPCTemp.listno),
              X9=(select inserted.X9 from inserted where inserted.listno=SpcB2B.dbo.BtoB_SPCTemp.listno),
              X10=(select inserted.X10 from inserted where inserted.listno=SpcB2B.dbo.BtoB_SPCTemp.listno),
              X11=(select inserted.X11 from inserted where inserted.listno=SpcB2B.dbo.BtoB_SPCTemp.listno),
              X12=(select inserted.X12 from inserted where inserted.listno=SpcB2B.dbo.BtoB_SPCTemp.listno),
              X13=(select inserted.X13 from inserted where inserted.listno=SpcB2B.dbo.BtoB_SPCTemp.listno),
              X14=(select inserted.X14 from inserted where inserted.listno=SpcB2B.dbo.BtoB_SPCTemp.listno),
              X15=(select inserted.X15 from inserted where inserted.listno=SpcB2B.dbo.BtoB_SPCTemp.listno),
              X16=(select inserted.X16 from inserted where inserted.listno=SpcB2B.dbo.BtoB_SPCTemp.listno),
              X17=(select inserted.X17 from inserted where inserted.listno=SpcB2B.dbo.BtoB_SPCTemp.listno),
              X18=(select inserted.X18 from inserted where inserted.listno=SpcB2B.dbo.BtoB_SPCTemp.listno),
              X19=(select inserted.X19 from inserted where inserted.listno=SpcB2B.dbo.BtoB_SPCTemp.listno),
              X20=(select inserted.X20 from inserted where inserted.listno=SpcB2B.dbo.BtoB_SPCTemp.listno),
              MeasQuality=(select inserted.MeasQuality from inserted where inserted.listno=SpcB2B.dbo.BtoB_SPCTemp.listno),
              MeasReason=(select inserted.MeasReason from inserted where inserted.listno=SpcB2B.dbo.BtoB_SPCTemp.listno),
              MeasSolution=(select inserted.MeasSolution from inserted where inserted.listno=SpcB2B.dbo.BtoB_SPCTemp.listno),
              Note=(select inserted.Note from inserted where inserted.listno=SpcB2B.dbo.BtoB_SPCTemp.listno)
          where SpcB2B.dbo.BtoB_SPCTemp.listno=(select listno from inserted)
      END
    IF NOT EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
      BEGIN
          --PRINT('DELETING')
          delete from SpcB2B.dbo.BtoB_SPCTemp where SpcB2B.dbo.BtoB_SPCTemp.listno=(select listno from deleted)
      END
END