CODEWITHSUNDEEP
sqloracle-databasetriggers
Federico
Federico

Reputation: 799

Using a table to store deleted rows

In my Oracle DB I've a table called tableA where an application write data. My program reads from tableA and when I've processed the data I delete them. The question is I want to keep a log of every data I've processed and I can't keep them in tableA because I've no control over application A and it might not work if I keep my processed data on that table, so I've created a table identical to tableA called tableB and I've put this trigger on tableA:

create or replace 
trigger tableA_delete_trigger
BEFORE DELETE ON tableA
FOR EACH ROW 
BEGIN
  INSERT INTO tableB
  ( a,
  b,
  c,
  )
  VALUES
  ( :old.a,
  :old.b,
  :old.c,
  sysdate);
END;

This system work quite well, the real problem is when I need to alter something in tableA I have to replicate by hand the same modification in tableB and if I add/remove coloumn I have to update the trigger.

Is there a better way to do this?

Upvotes: 0

Views: 255

Answers (2)

Armunin
Armunin

Reputation: 996

What about an alter trigger:

CREATE OR REPLACE TRIGGER ddl_trigger AFTER ALTER ON schema
DECLARE
    cmd VARCHAR2(32000);
BEGIN
    SELECT
        upper(sql_text)
    INTO
        cmd
    FROM
        v$open_cursor
    WHERE
        upper(sql_text) LIKE 'ALTER TABLE TABLEA%' ;
    SELECT
        REPLACE(cmd, 'TABLEA', 'TABLEB')
    INTO
        cmd
    FROM
        dual;
    EXECUTE IMMEDIATE cmd;
END;

Not sure that will work because of the recursion.

Upvotes: 0

mustaccio
mustaccio

Reputation: 19004

An alternative approach might be to rename TableA and create a view named TableA for application A to use. You would then logically delete rows by whatever means seem appropriate and only expose in the view the rows that are not deleted.

You would still need to modify the view if the table structure changes, but at least you won't need to worry about the trigger.

Upvotes: 2

Related Questions