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.

Microsoft SQL ServerMicrosoft SQL Server 2005

Last Comment

captnapalm

8/22/2022 - Mon

jaryco

2/27/2009

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

captnapalm

2/27/2009

ASKER

This returns:

Msg 207, Level 16, State 1, Procedure trackingno, Line 16
Invalid column name 'UPS_IMPORT'.

ASKER CERTIFIED SOLUTION

jaryco

2/27/2009

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

jaryco

2/27/2009

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

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat

William Peck

captnapalm

2/27/2009

ASKER

Thanks, this worked.