Deleted Records Backup script using Trigger in Oracle

Deleted Records Backup Script using Trigger in Oracle

Create Table Script to Backup the Deleted Records
CREATE TABLE xx_trg_test(trx_id NUMBER, trx_number VARCHAR2(30),trx_date DATE, delete_date date, deleted_by VARCHAR2(30)) 

Delete Script to Delete the Records
delete from ra_customer_trx_all where  CUSTOMER_TRX_ID=1083


Sample trigger for Backup the Deleted records after Deleting
CREATE OR REPLACE TRIGGER trx_orders_before_delete
   BEFORE DELETE
   ON ra_customer_trx_all
   FOR EACH ROW
DECLARE
   v_username   VARCHAR2 (10);
BEGIN
   -- Find username of person performing the DELETE on the table
   SELECT USER
     INTO v_username
     FROM DUAL;

   -- Insert record into audit table
   INSERT INTO xx_trg_test
               (trx_id, trx_number, trx_date,
                delete_date, deleted_by
               )
        VALUES (:OLD.customer_trx_id, :OLD.trx_number, :OLD.trx_date,
                SYSDATE, v_username
               );
END;

Check the original table for Deleted Records  

Select * from ra_customer_trx_all where  CUSTOMER_TRX_ID=1083  


Check the Deleted Records in Custom Table

Select * from xx_trg_test