Trigger to copy deleted rows to another identical table with Cascade Delete On

Posted on

Question :

I have two tables.
Sale and SaleProducts

SaleProducts contains all the products that have a Sale.

1 sale, many products. (1 to many)

SaleProducts has a relation to Sale with On Delete set to CASCADE.

When I delete a Sale it deletes (because of the On Delete cascade) all the SaleProducts related to the Sale.

Can i define a TRIGGER like ON DELETE that will COPY before deleting, all the rows into an identical tables of Sale and SaleProducts for History saving ?

Answer :

Define trigger BEFORE DELETE on main (Sale) table which will copy all records from slave (SaleProducts) table, which may be deleted by cascade operation, to an archive table.

Define AFTER DELETE trigger which will copy the record deleted from main table.

If there are records in both archives – the deletion was carried out. All records in both archive tables are valid.

If there are records in SaleProducts archive and there is no appropriate record in Sale archive – the delete operation was not performed due to some error, and those records must be removed from SaleProducts archive table.

Leave a Reply

Your email address will not be published. Required fields are marked *