| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Accessing a Row in a Delete trigger
On 22 Jan 1998 18:10:41 GMT, "Michael Roynan" <mike.roynan_at_memo.ikea.com> wrote:
>From a delete trigger, I'm trying to insert the row being deleted from
>a production table into an archive table before the delete takes
>place. I want to stay away from listing each individual column name
>in the insert statement in the event the table is modified/recreated.
>Is there a way to reference the whole row so I can insert it into the
>archive table?
>
>I've tried using dynamic sql to create and parse a command with the
>:old column names, but it thinks the :old.% columns are bind variables
>and returns an error.
>
>If it's not possible, is there any other method to insert the row to
>another table before
>it is deleted by the trigger?
>
>Thanks in advance,
You can do this, but not in the way you were attempting. You cannot dynamically access the :new and :old values in a trigger. What you can do though to reduce the coding you have to do is to write a procedure to generate the trigger for a given table. The following demonstrates the idea and sets up the triggers for EMP and DEPT (and note save_changes below, you cannot just compare old and new using <>, you must also consider NULLS as well in the comparision). This example create an audit trigger for after UPDATE (not delete so its not your exact example) trigger that will save all changed values for a row into another table:
create table changes
( tname varchar2(30), cname varchar2(30), oldv varchar2(2000), newv varchar2(2000),
create or replace procedure save_changes( p_tname in varchar2,
p_cname in varchar2,
p_oldv in varchar2,
p_newv in varchar2 )
is
if ( p_oldv <> p_newv or
( p_oldv is not null and p_newv is null ) or
( p_oldv is null and p_newv is not null ) )
then
insert into changes ( tname, cname, oldv, newv, timestamp )
values ( p_tname, p_cname, p_oldv, p_newv, sysdate );
end if;
create or replace procedure gen_trigger( p_tname in varchar2 ) as
l_tname varchar2(30) default upper(p_tname);
l_stmt varchar2(255) default
'save_changes( ''' || l_tname || ''', ''$C$'', :old.$C$, :new.$C$ );';
begin
dbms_output.put_line( 'create or replace trigger audit_' || l_tname );
dbms_output.put_line( 'after update on ' || l_tname );
dbms_output.put_line( 'for each row' );
dbms_output.put_line( 'begin' );
for x in ( select column_name
from user_tab_columns
where table_name = l_tname )
loop
dbms_output.put_line( chr(9)||replace(l_stmt,'$C$', x.column_name));
end loop;
set serveroutput on
set feedback off
spool tmp.sql
exec gen_trigger('emp')
exec gen_trigger('dept')
spool off
set feedback on
@tmp.sql
If you granted your self "create trigger", you could actually have the gen_trigger routine create the trigger instead of just printing the trigger out to be created...
An enhancement to this would have you move the procedure save_changes into a package and overload it so that it worked better for dates and numbers (eg; it would to_char(thedate, 'dd-mon-yyyy hh24:mi:ss') before inserting it as a varchar so as to not lose the precision of the date....
In Oracle8, there exists a new and different way to do this, just in case you are interested, it could look like this:
SQL> create table demo_tbl1
2 ( x int primary key, 3 y int, 4 z int
Table created.
SQL>
SQL> create table demo_tbl1_history
2 ( x int, 3 y int, 4 z int, 5 timestamp date
Table created.
SQL>
SQL> create view demo_view1
2 as select * from demo_tbl1
3 /
View created.
SQL>
SQL> create or replace trigger demo_view1_bdfer
2 instead of delete on demo_view1
3 for each row
4 begin
5 insert into demo_tbl1_history 6 select a.*, sysdate from demo_tbl1 a 7 where x = :old.x; 8 8 delete from demo_tbl1 9 where x = :old.x;
Trigger created.
SQL>
SQL> grant all demo_view1 to public;
SQL>
SQL> insert into demo_view1
2 select user_id, 1, 2
3 from all_users
4 where rownum < 10;
9 rows created.
SQL>
SQL> alter session set nls_date_format = 'dd-mon-yy hh24:mi:ss';
Session altered.
SQL>
SQL> select * from demo_view1;
X Y Z
---------- ---------- ----------
0 1 2
5 1 2
17 1 2
19 1 2
20 1 2
1791 1 2
1792 1 2
1813 1 2
1793 1 2
9 rows selected.
SQL> select * from demo_tbl1_history;
no rows selected
SQL>
SQL> delete from demo_view1 where mod(x,2) = 0;
3 rows deleted.
SQL>
SQL> select * from demo_view1;
X Y Z
---------- ---------- ----------
5 1 2
17 1 2
19 1 2
1791 1 2
1813 1 2
1793 1 2
6 rows selected.
SQL> select * from demo_tbl1_history;
X Y Z TIMESTAMP
---------- ---------- ---------- ------------------
0 1 2 23-jan-98 12:51:19
20 1 2 23-jan-98 12:51:19
1792 1 2 23-jan-98 12:51:19
SQL> exit
The concept here is that you would not use demo_tbl1 as the real table, you would always access it via the view (grant all on the view, not on the table). All deletes via the view capture the rows to be deleted in one table, and then delete the row....
Hope this helps..
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Jan 23 1998 - 00:00:00 CST
 |
 |