For Delete trigger with condition on a joined table

Question

I am needing to create a trigger to load deleted data records into a history table. However I want to only insert those deleted records based off a condition of a field on another table.

I've created a trigger like this

CREATE TRIGGER [dbo].[bwrTriggerAuditRecord] on [dbo].[Borrower]
AFTER DELETE AS
BEGIN
INSERT INTO AuditLog (TableName,TableID, DeletedOn)
            SELECT 'Borrower' AS TableName ,ssn, GETDATE() 
FROM deleted
END

This works fine however, I am needing to add a condition so the trigger only inserts those records based on a field condition located in a joined table

Ideally if it were possible I would want to do something like this:

CREATE TRIGGER [dbo].[bwrTriggerAuditRecord] on [dbo].[Borrower]
FOR DELETE AS
INSERT INTO AuditLog (TableName,TableID, DeletedOn)
SELECT 'Borrower' AS TableName ,
       i.ssn,
       GETDATE()
FROM deleted i
join borrower bwr on i.borrowerid=bwr.borrowerid
join filedata fd on (bwr.FileDataID = fd.FileDataID )
INNER JOIN (select OrganizationID from Organization where code not like '26%') org on (fd.OrganizationID = org.OrganizationId)
where fd.filename not like '26%' 
and fd.filename not like '%.copy' and fd.filename not like '%+'

Apparently this wont work since the record on the deleted table doesn't really exist.

My question is, what would be another way to achieve this? Is there perhaps a BEFORE DELETE trigger I should use? Should I maybe use two triggers to get the information I need so that the deleted record can find the fields I am looking for?

Answer 1

Have you tried:

CREATE TRIGGER [dbo].[bwrTriggerAuditRecord] on [dbo].[Borrower]
AFTER DELETE AS
INSERT INTO AuditLog (TableName,TableID, DeletedOn)
SELECT 'Borrower' AS TableName ,
       i.ssn,
       GETDATE()
FROM deleted i
join filedata fd on (i.FileDataID = fd.FileDataID )
INNER JOIN (select OrganizationID from Organization where code not like '26%') org on (fd.OrganizationID = org.OrganizationId)
where fd.filename not like '26%' 
and fd.filename not like '%.copy' and fd.filename not like '%+'

You shouldn't need to tie back to the Borrower table - the deleted table should hold all the same columns as Borrower did.