CODEWITHSUNDEEP
oracle-databaseplsqltriggers
ernestmov
ernestmov

Reputation: 1

How to know what record(s) was updated, deleted, or inserted in Oracle

I'm doing a LogTable, where I save the data that was modified on the RealTable.

By data I mean that I need to recover the whole record's :new and :old values. So I have a trigger that reacts whether there's an insert, delete or update.

So my question is: How do I know which record was changed and how do I recover it's contents?

example:

create table t1(
    a integer,
    b integer
)

create table LogT1(
    new_a integer, 
    old_a integer,
    new_b integer,
    old_b integer
)

Let's say there's a record on t1 with the values a=1, b=2 and t1 gets an update so the record changes the values to a=3, b=4

So that update activates the trigger and it must:

  1. Know which record was modified.
  2. Recover that record.
  3. Get the "before" and "after" an and b values
  4. Insert those values into LogT1 as old_a, new_a, old_b, new_b

resulting as an insert on LogT1 with the values new_a=3, old_a=1, new_b=4, old_b=2

CREATE or REPLACE TRIGGER tr001
  AFTER INSERT OR UPDATE OR DELETE ON t1

DECLARE

    a integer;
    b integer;
    a2 integer;
    b2 integer;

BEGIN

    IF DELETING THEN 

        *Recover the row that changed* 

        *Then use that row*
        a2:= :new.a ;
        b2:= :new.b ;
        a:= :old.a ;
        b:= :old.b ;
        insert into LogT1 (new_a integer, old_a integer, new_b integer, old_b integer) 
values (a2,a,b2,b);

    END IF;

....

END tr001;

I'm new at this so my most sincere apology if my question is not proper.

Upvotes: 0

Views: 3287

Answers (4)

miherrma
miherrma

Reputation: 336

As far as I remember you might declare the trigger as FOR EACH ROW so that it is fired for each row that is deleted|inserted|updated, then the :new and :old contain the specific row values. Hope this helps.

CREATE or REPLACE TRIGGER tr001 
  AFTER INSERT OR UPDATE OR DELETE 
  ON t1
  FOR EACH ROW
DECLARE
...

Upvotes: 4

Matthew Watson
Matthew Watson

Reputation: 14233

If you have 11g, you can use Flashback Data Archive to do this,

Upvotes: 1

SRINU
SRINU

Reputation: 1

Actually you can recover the old data, if you know the time at which the data required, without creating the log table using following query. It may satisfy your requirement.

SELECT * FROM TABLE_NAME AS OF TIMESTAMP

(Provide the time at which time the records required as the timestamp)

Upvotes: 0

Flukey
Flukey

Reputation: 6555

This is some PL/SQL I use in a project to do pretty much the exact thing you want.

I've simplified my script (it was doing some other functionality):

create or replace trigger rtt.course_log
       after insert or update or delete
       on rtt.TRAINING_COURSE
       for each row

declare msg varchar2(255);

begin
        if updating then
           msg := 'updating course: ' ||  :new.name || 'at: ' || :new.updated_at;
        elsif inserting then
           msg := 'creating course: ' ||  :new.name || 'at: ' || :new.updated_at;
        elsif deleting then
           msg := 'deleting course: ' ||  :new.name || 'at: ' || :new.updated_at;
        end if;
        insert into rtt.TRAINING_LOG (message, created_at) values (msg, SYSDATE);
end;

Upvotes: 0

Related Questions