We help IT Professionals succeed at work.

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

RPIS
RPIS asked
on
Medium Priority
721 Views
Last Modified: 2006-11-18
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.
Comment
Watch Question

inserted.idnum

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
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.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.