In SQL Trigger, getting "Ambiguous Column Name" error.

Assuming that a DTS package will act as an insert function on table Employees, I am trying to trigger creation of a SFT_Machine_Assignment record when one does not exist.  Using the code below, gleaned from experts-exchange, I keep getting Error 209: Ambiguous Column Name 'IDNum'.  (Line4)

CREATE TRIGGER [MachAssignmentUpdate] ON [dbo].[EMPLOYEES]
after INSERT
AS
insert into SFT_Machine_Assignments (IDNum)
select IDNUM from inserted
left outer join SFT_Machine_Assignments
on inserted.IDNUM = SFT_Machine_Assignments.IDNum
where SFT_Machine_Assignments.IDNum is null

SFT_Machine_Assignment only has three fields, id, IDNum, Mach-Type - with a default of 'Unassigned'.
Employees has Uniqueid, IDNUM, etc.

How else can you qualifiy the IDNum in the Insert statement?  If that is the problem.
RPISAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
CREATE TRIGGER [MachAssignmentUpdate] ON [dbo].[EMPLOYEES]
after INSERT
AS
insert into SFT_Machine_Assignments (IDNum)
select i.IDNUM from inserted as i
left outer join SFT_Machine_Assignments sma
on i.IDNUM = sma.IDNum
where sma.IDNum is null
0
 
MikeWalshCommented:
inserted.idnum

0
 
MikeWalshCommented:
sorry.. my screen did a funny thing here, too much at once. I was explaining why it wasn't working. WHen you do a join you have to fully qualify any column names that appear in more than one table. As a good rule of thumb you should always qualify the name regardless of whether it appears in more than one, just as Angel showed above.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.