backp updated row

[Fuhrer]

hi, at oracle db i want to backup a row when an update request come for the row. how can i make this with pl/sql?

[Abhishek Sur]

use Update Trigger. Whenever the row is updated use :old and :new objects to get the data from updated row and new row. If it is after update trigger, you need to use :old to get old values otherwise use select from table to get the value. Now insert them to the log table. For instance :

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;

:)

Abhishek Sur **Don't forget to click "Good Answer" if you like this Solution.

---

My Latest Articles-->** Simplify Code Using NDepend
Basics of Bing Search API using .NET
Microsoft Bing MAP using Javascript