Can't get SQL IF statement to work - getting:The multi-part identifier "d.ExpiryDate" could not be bound.

Question

I am trying to call an IF statement on my trigger so it won't archive expired files. (I only want to keep files that have been deleted but have not been expired)

My error is The multi-part identifier "d.ExpiryDate" could not be bound.

My Code:

    ALTER TRIGGER [dbo].[ArchiveDB] 
   ON  [dbo].[TBL_Content] 
   AFTER DELETE
AS 

BEGIN

declare @ContentID int

set @ContentID = (select ContentID from deleted)

IF (d.ExpiryDate > getDate() )
    begin
        insert into ArchiveBackup.dbo.TBL_Deleted_Content
        (ContentID, StartDate, ExpiryDate, Title... etc)
        select 
        d.ContentID,d.StartDate,d.ExpiryDate,d.Title... etc 
        from deleted as d
    end

END

Thanks for the help!

Answer 1

You'd have to tell SQL Server where to find the ExpiryDate, like:

if ((select ExpiryDate from deleted) > getdate())

Be aware that a trigger can be called for cases when multiple rows where deleted. It might be better to replace the entire if contruct with a query:

insert into ArchiveBackup.dbo.TBL_Deleted_Content
(ContentID, StartDate, ExpiryDate, Title... etc)
select 
d.ContentID,d.StartDate,d.ExpiryDate,d.Title... etc 
from deleted as d
where ExpiryDate > getdate()

Or even better, write a stored procedure that deletes rows instead of deleting them directly from the table. Stored procedures are way easier to understand and maintain than triggers.

Answer 2

There is no table or view anywhere that you alias as d - no wonder it can't find it!

You need to use the full table name in this case:

ALTER TRIGGER [dbo].[ArchiveDB] 
  ON  [dbo].[TBL_Content] AFTER DELETE
AS BEGIN

declare @ContentID int

set @ContentID = (select ContentID from deleted)

IF (deleted.ExpiryDate > getDate() )
BEGIN
        insert into ArchiveBackup.dbo.TBL_Deleted_Content
        (ContentID, StartDate, ExpiryDate, Title... etc)

        select 
        d.ContentID,d.StartDate,d.ExpiryDate,d.Title... etc 
        from deleted as d
END
END

The alias d is only ever declared and used inside the BEGIN ... END block - it is not visible outside that block! You can only use a table alias in the statement that declares it - it's not globally visible.

Answer 3

I believe this is what you need, as this will properly handle cases where you delete more than one row at a time as well as check the condition that you want for each row.

ALTER TRIGGER [dbo].[ArchiveDB] 
 ON  [dbo].[TBL_Content] 
 AFTER DELETE
AS 

BEGIN

insert into ArchiveBackup.dbo.TBL_Deleted_Content (ContentID, StartDate, ExpiryDate, Title... etc)
  select 
    d.ContentID,d.StartDate,d.ExpiryDate,d.Title... etc 
  from 
    deleted as d
  where
    d.ExpiryDate > getDate()

END