Oracle trigger to keep a copy of deleted records in another table I am using this trigger to keep a copy of the deleted record from table Orders_Details in table Orders_Details_Deleted.

I am using this trigger for two reasons:


security, in case the record is accidentally deleted
to keep a history of deleted records by the user


Is there any better way?

CREATE OR REPLACE TRIGGER TRG_Record_Deleted
   BEFORE DELETE
   ON Orders_Details
   FOR EACH ROW
BEGIN
   INSERT INTO Orders_Details_Deleted
      SELECT *
        FROM Orders_Details
       WHERE Orders_Details.Order_ID = Orders_Details.Order_ID;
END;
I am using this trigger to keep a copy of the deleted record from table Orders_Details in table Orders_Details_Deleted.


No, you're not. The code copies all rows from orders_details to order_details_deleted every time the trigger is run.


  Is there any better way [than using a trigger] ?


Yes, there is a better way. Don't reinvent database features but use native functionality instead. Have a look for the following Oracle features:

Flashback Data Archive


  A Flashback Data Archive provides the ability to track and store transactional changes to a table over its lifetime. A Flashback Data Archive is useful for compliance with record stage policies and audit reports.


Auditing


  Auditing is the ability to track changes that users make in the database.


Workspace Manager


  Keep a history of changes to data
  
  You can navigate workspaces and row versions to view the database as of a particular milestone or point in time. You can roll back changes to a row or table in a workspace to a milestone. A typical example might be a land information management application where Workspace Manager supports regulatory requirements by maintaining a history of all changes to land parcels.