This discussion is archived
2 Replies Latest reply: Feb 28, 2013 2:04 PM by rp0428 RSS

How to select all columns in a trigger?

877720 Newbie
Currently Being Moderated
I add a "before delete" trigger on a table, in order to insert the records to a backup table before they are deleted.

but I cannot write like this : insert into t_backup select * from :old;

The table has more than 30 columns so I don't want to select them one by one, how can I select all columns?
  • 1. Re: How to select all columns in a trigger?
    Girish Sharma Guru
    Currently Being Moderated
    Yes, it is possible by writing something like this :

    where col1 = :old.col1; But it is not directly supported. First, we need a package to remember all the OLD records:.... and please see below link for complete code in action :

    http://www.dbforums.com/oracle/925729-trigger-back-up-data.html

    What I am doing here, just copying the code and replacing "emp" with "test" (The table name on which I am going to apply this using notepad find and replace):
    SQL> select * from test_backup;
    
    no rows selected
    
    SQL> select * from test;
    
             A
    ----------
             1
             2
             4
             5
    
    SQL> select * from test_backup;
    
    no rows selected
    
    SQL>
    
    create or replace package test_trg_pkg as
      type test_type is table of test%ROWTYPE index by binary_integer;
      test_tab test_type;
    end;
    /
    
    create or replace trigger test_bds before delete on test
    begin
      test_trg_pkg.test_tab.delete;
    end;
    /
    
    create or replace trigger test_adr after delete on test
    for each row
    declare
      -- To allow us to select the old values
      pragma autonomous_transaction;
    begin
      select *
      into   test_trg_pkg.test_tab(test_trg_pkg.test_tab.COUNT+1)
      from   test
      where  a = :old.a;  <----- Here you have to give your column name.
    end;
    /
    
    create or replace trigger test_ads after delete on test
    begin
      for i in 1..test_trg_pkg.test_tab.COUNT loop
        insert into test_backup values test_trg_pkg.test_tab(i);
      end loop;
    end;
    /
    
    SQL> delete from test where a=1;
    
    1 row deleted.
    
    SQL> select * from test_backup;
    
             A
    ----------
             1
    
    SQL> delete from test where a=2;
    
    1 row deleted.
    
    SQL> select * from test_backup;
    
             A
    ----------
             1
             2
    
    SQL>
    Regards
    Girish Sharma
  • 2. Re: How to select all columns in a trigger?
    rp0428 Guru
    Currently Being Moderated
    >
    I add a "before delete" trigger on a table, in order to insert the records to a backup table before they are deleted.

    but I cannot write like this : insert into t_backup select * from :old;

    The table has more than 30 columns so I don't want to select them one by one, how can I select all columns?
    >
    You can't 'select all columns'. You have to refer to each one by name.

    See the 'Correlation Names and Pseudorecords' section of the PL/SQL Language doc
    http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/triggers.htm#sthref775
    >
    OLD, NEW, and PARENT are also called pseudorecords, because they have record structure, but are allowed in fewer contexts than records are. The structure of a pseudorecord is table_name%ROWTYPE, where table_name is the name of the table on which the trigger is created (for OLD and NEW) or the name of the parent table (for PARENT).

    The restrictions on pseudorecords are:

    •A pseudorecord cannot appear in a record-level operation.

    For example, the trigger cannot include this statement:

    :NEW := NULL;
    •A pseudorecord cannot be an actual subprogram parameter.

    (A pseudorecord field can be an actual subprogram parameter.)
    . . .
    >
    There is no way to refer to the 'record' as a whole.

    There are already plenty of ways to capture change data: change data capture, flashback functionality, add an MV log to the table. Even trying to do this in a trigger is definitely NOT the way to do it.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points