2 Replies Latest reply: Feb 28, 2013 4:04 PM by rp0428 RSS

    How to select all columns in a trigger?

    877720
      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
          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
            >
            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.