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
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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


Guy Hengel [angelIII / a3]Billing EngineerCommented:
CREATE TRIGGER [MachAssignmentUpdate] ON [dbo].[EMPLOYEES]
after INSERT
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.