ERPise ERPise - 1 year ago 62
SQL Question

How to reference column on inserted table in after trigger

I have an update to process on a trigger when a specific code in the column is input. When I reference the inserted table, which shows in the intellisence dropdown list when I type inserted I get the error:

The multipart identifier "" could not be bound.

Any idea how to reference a column in the inserted table for a trigger? Also, why is it better to use the inserted table than the existing table?

USE PCUnitTest

P.First_Name = T.ForeName
,P.Middle_Name = T.Middle_Name
,P.Last_name = T.Surname
FROM GMUnitTest.dbo.Contact1 C
INNER JOIN PCUnitTest.dbo.People P
ON P.People_ID = C.Key4
CROSS APPLY dbo.NameParser(inserted.Contact) T --"the multipart identifier "" could not be bound"
WHERE C.Key1 = '76'

Answer Source

You bring in inserted just like any table:

    SET P.First_Name = T.ForeName,
        P.Middle_Name = T.Middle_Name,
        P.Last_name = T.Surname
FROM PCUnitTest.dbo.People P JOIN
     GMUnitTest.dbo.Contact1 C
     ON P.People_ID = C.Key4 JOIN
     inserted i
     ON i.?? = c.?? CROSS APPLY
     dbo.NameParser(i.Contact) T 
WHERE C.Key1 = '76';

The only question is what columns you use for the JOIN. The ?? are for your column names.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download