• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 589
  • Last Modified:

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.
0
RPIS
Asked:
RPIS
  • 2
2 Solutions
 
MikeWalshCommented:
inserted.idnum

0
 
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:
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

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now