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

Posted on 2006-04-04
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
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.
Question by:RPIS
    LVL 13

    Expert Comment


    LVL 142

    Accepted Solution

    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
    LVL 13

    Assisted Solution

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now