This content has been marked as final. Show 2 replies
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 :
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>
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
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.