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?