Prev: Importing nightly from another DB
Next: Create view to get data from multiple databases
From: DavidC on 16 Jun 2010 18:18
I have a delete trigger that is trying to get information during the delete
using the deleted object and I am getting the errors below.

The multi-part identifier "deleted.RepCompanyID" could not be bound.
The multi-part identifier "deleted.BrandID" could not be bound.

The trigger code giving the error is below.
p.s. This is from an old database so alternatives in constraint use would be
ok. Thanks.

DECLARE @RepCompany varchar(200);
DECLARE @Brand nvarchar(50);
DECLARE @OldInfo varchar(200);
SET @RepCompany = (SELECT RepCompany FROM dbo.RepCompany WHERE
RepCompanyID = deleted.RepCompanyID);
SET @Brand = (SELECT Brand FROM dbo.Brands WHERE BrandID =
deleted.BrandID);
SET @OldInfo = @RepCompany + '-Brand=' + @Brand;


--
David
From: Eric Isaacs on 16 Jun 2010 19:49
This is what you're looking for:

SELECT
@RepCompany = RepCompany.RepCompany
@Brand = Brands.Brand
FROM deleted
LEFT JOIN dbo.RepCompany ON RepCompany.RepCompanyID =
deleted.RepCompanyID
LEFT JOIN dbo.Brands ON Brands.BrandID = deleted.BrandID

SET @OldInfo = @RepCompany + '-Brand=' + @Brand;

But what you'll find is that it only works when one row is deleted at
a time. If Delete * from RepCompany is executed, or Delete * from
RepCompany where BrandID = 123 is executed then only one row will be
tracked by this delete trigger. You should be doing it in sets using
the delete table instead of one row at a time from the delete table.

I hope that helps.

-Eric Isaacs
 | 
Pages: 1
Prev: Importing nightly from another DB
Next: Create view to get data from multiple databases