Trigger Error ? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Trigger Error ?

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2002-05-03 : 18:15:29

Hello! Everyone :)

This is the trigger that I am trying to run in Query Analyzer. but I am keep getting error message saying that "
Server: Msg 209, Level 16, State 1, Procedure Validate_OrderRecord, Line 11
Ambiguous column name 'EmployeeID'.
Server: Msg 209, Level 16, State 1, Procedure Validate_OrderRecord, Line 14
Ambiguous column name 'CustomerID'.
Server: Msg 209, Level 16, State 1, Procedure Validate_OrderRecord, Line 17
Ambiguous column name 'ProductID'."

This trigger will check if the record being loaded into the staging table has a valid EmployeeID, customerID, and ProductID, and If not , insert it into the error log table for auditing purposes.The inserted table referenced in the erroe handler is an insert/update trigger alias for the record currently being inserted.

USE Northwind
GO
CREATE TRIGGER Validate_OrderRecord
ON dbo.Order_Import
FOR INSERT, UPDATE
AS
-- create a variable for our error message
DECLARE @errMsg varchar(50)

IF EXISTS(SELECT EmployeeID FROM dbo.Employees e
INNER JOIN inserted i
ON e.EmployeeID = i.EmployeeID)
IF EXISTS(SELECT CustomerID FROM dbo.Customers c
INNER JOIN inserted i
ON c.CustomerID = i.CustomerID)
IF EXISTS(SELECT ProductID FROM dbo.Products p
INNER JOIN inserted i
ON p.ProductID = i.ProductID)
-- record is ok, so quit
RETURN
ELSE
-- invalid ProductID
BEGIN
SET @errMsg = 'Invalid ProductID'
GOTO errhandler
END
ELSE
-- invalid CustomerID
BEGIN
SET @errMsg = 'Invalid CustomerID'
GOTO errhandler
END
ELSE
-- invalid EmployeeID
BEGIN
SET @errMsg = 'Invalid EmployeeID'
GOTO errhandler
END

errhandler:
BEGIN
INSERT INTO dbo.Order_ImportErrors(EmployeeID,
CustomerID, OrderDate, RequiredDate,
ProductID, Quantity, Discount, ErrorDesc)
SELECT i.EmployeeID, i.CustomerID, i.OrderDate,
i.RequiredDate, i.ProductID, i.Quantity,
i.Discount, @errMsg
FROM inserted i

RETURN
END

#### Any tips would be appreciated it ..

Thanks
John

Nedra
Starting Member

21 Posts

Posted - 2002-05-03 : 20:11:20

You need to qualify the column with table that its coming from like this:

IF EXISTS(SELECT i.EmployeeID FROM dbo.Employees e
INNER JOIN inserted i
ON e.EmployeeID = i.EmployeeID)
IF EXISTS(SELECT i.CustomerID FROM dbo.Customers c
INNER JOIN inserted i
ON c.CustomerID = i.CustomerID)
IF EXISTS(SELECT i.ProductID FROM dbo.Products p
INNER JOIN inserted i
ON p.ProductID = i.ProductID)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources