1
\$\begingroup\$

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:

  1. security, in case the record is accidentally deleted
  2. 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;
\$\endgroup\$
2
  • \$\begingroup\$ Do you need to actually delete the row? You could just have a column in the table to indicate it has been deleted/voided. \$\endgroup\$
    – forsvarir
    Commented Jul 29, 2016 at 7:59
  • \$\begingroup\$ @forsvarir its for two reason first one is security in case the record accidentally deleted and to keep history of deleted record by user \$\endgroup\$
    – samer
    Commented Jul 29, 2016 at 8:09

1 Answer 1

3
\$\begingroup\$

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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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