CODEWITHSUNDEEP
databaseloggingdata-structures
Cesar Romeroo
Cesar Romeroo

Reputation: 193

How to make a changes Log of a orders system

I've seen stackoverflow's question changes log and is very rich;

i have a system that register product Orders but that orders change very often What is the database structure to save and have access to an order and see the order versions?

My tables are:

Clients
Orders
Articles_Order
in Articles_Orders is where the order code, the product code and the quantity is stored and is what changes (adding products etc) or where i can find a example of that?

Upvotes: 0

Views: 36

Answers (1)

Justin Killen
Justin Killen

Reputation: 737

There is an example of how do to this using update triggers here. Following is a copy/paste snippet of that page:

CREATE TABLE orders
    ( order_id number(5),
      quantity number(4),
  cost_per_item number(6,2),
  total_cost number(8,2)
);


CREATE OR REPLACE TRIGGER orders_after_update
AFTER UPDATE
   ON orders
   FOR EACH ROW

DECLARE
   v_username varchar2(10);

BEGIN

   -- Find username of person performing UPDATE into table
   SELECT user INTO v_username
   FROM dual;

   -- Insert record into audit table
   INSERT INTO orders_audit
   ( order_id,
     quantity_before,
     quantity_after,
     username )
   VALUES
   ( :new.order_id,
     :old.quantity,
     :new.quantity,
     v_username );

END;

Upvotes: 1

Related Questions