Link to home
Create AccountLog in
Avatar of captnapalm
captnapalm

asked on

multi-part identifier could not be bound in where clause of trigger

Hello,
I'm somewhat of a SQL noob, trying to create a trigger that will update a field in table A whenever a new entry is added to table B.

I understand that joins are not allowed in updates as of SQL 2005. I'm getting the following error:
Msg 4104, Level 16, State 1, Procedure trackingno, Line 15
The multi-part identifier "UPS_IMPORT.BOX_ID" could not be bound so I'm using a where clause to compare fields.

Here is my SQL

ALTER TRIGGER [trackingno]
   ON  [dbo].[UPS_IMPORT]
   AFTER INSERT,DELETE,UPDATE
AS
BEGIN
      SET NOCOUNT ON;
UPDATE BOX set TRACKINGNO = (SELECT TRACKINGNO from UPS_IMPORT WHERE UPS_IMPORT.BOX_ID = BOX.BOX_ID) WHERE UPS_IMPORT.BOX_ID = BOX.BOX_ID

END

Any solutions or suggestions would be appreciated.
Avatar of jaryco
jaryco
Flag of Costa Rica image

Try this:
ALTER TRIGGER [trackingno]
ON [dbo].[UPS_IMPORT] 
AFTER INSERT
AS BEGIN
    SET NOCOUNT ON;
 
    UPDATE B
    set TRACKINGNO = I.TRACKINGNO
    FROM BOX B 
    JOIN INSERTED I ON I.UPS_IMPORT.BOX_ID = B.BOX_ID
END

Open in new window

Avatar of captnapalm
captnapalm

ASKER

This returns:

Msg 207, Level 16, State 1, Procedure trackingno, Line 16
Invalid column name 'UPS_IMPORT'.
ASKER CERTIFIED SOLUTION
Avatar of jaryco
jaryco
Flag of Costa Rica image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
What matter here is that INSERTED contains your UPS_IMPORT inserted row. So you take that BOX_ID to update your BOX table.
ALTER TRIGGER [trackingno]
ON [dbo].[UPS_IMPORT] 
AFTER INSERT
AS BEGIN
    SET NOCOUNT ON;
 
    UPDATE B
    set TRACKINGNO = I.TRACKINGNO
    FROM BOX B 
    JOIN INSERTED I ON I.BOX_ID = B.BOX_ID
END

Open in new window

Thanks, this worked.