1

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?

3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.