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?