Hi Tom,
I'm trying to get the value from the ":OLD.xxxxx" column in a before delete trigger. The problem is I don't know the name of the column, as the column(s) are the ones defined in the primary key.
create or replace trigger bef_del_pl_item
before delete on pl_item
for each row
Declare
cursor find_pk_col is
select cc.column_name c_name
from user_cons_columns cc, user_constraints c
where c.TABLE_NAME = 'PL_ITEM'
and cc.TABLE_NAME = c.TABLE_NAME
and c.CONSTRAINT_TYPE = 'P'
and cc.CONSTRAINT_NAME = c.CONSTRAINT_NAME;
string varchar2(2000) :='';
field varchar2(30);
first_col boolean := true;
Begin
for rec in find_pk_col loop
field := ':old.' ||rec.c_name;
dbms_output.put_line(field); -- for test. Now I have the column name
if not first_col then
string := string ||'#';
end if;
string := string || rec.c_name || ':' || field; --it's here I wants the contents of the :old value from column "field". How to get that?
dbms_output.put_line(string); -- for test
end loop;
End;
/
So the outcome of "string should look something like "column_name:value" and if there is more than one column in the primary key then "column_name:value#column_name:value ....."
Later I will replace the "table_name" in my cursor then a variable, as you have described in
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:542325000346769711 Thanks for your help Tom.
Br
Kim
Yes you are right. The goal is to write a generic code. We have around 75 tables, where we (a customer) wants to capture all deletes from those tables. They wants user, date, table and then the primary key to be kept in an audit table. So I try to write some generic code (before delete trigger) to each table that can capture these information and put them into our "audit table". It will save a lot of work to have this generic and not to manual create each trigger. To capture the table name, I have used your code form the link showen above.
Thanks,
Kim
. It will save a lot of work to have this generic and not to manual create each trigger.No it won't - simply because *it cannot work*.
However, what WILL work and will work well would be to write something that GENERATES the trigger.
something like this:
ops$tkyte%ORA11GR2> create table t1( x int, y int, z int, primary key(x,y) );
Table created.
ops$tkyte%ORA11GR2> insert into t1 values ( 1, 2, 3 );
1 row created.
ops$tkyte%ORA11GR2> create table t2( a int, b int, c int, primary key(a) );
Table created.
ops$tkyte%ORA11GR2> insert into t2 values ( 1, 2, 3 );
1 row created.
ops$tkyte%ORA11GR2> create table t3( a int, b int, c int, primary key(c,a,b) );
Table created.
ops$tkyte%ORA11GR2> insert into t3 values ( 1, 2, 3 );
1 row created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create or replace procedure generate_trigger( p_tname in varchar2 )
2 AUTHID CURRENT_USER
3 as
4 l_trigger clob;
5 l_create clob;
6 l_crlf varchar2(2) := chr(10);
7 cursor c is
8 select cc.column_name c_name
9 from user_cons_columns cc, user_constraints c
10 where c.TABLE_NAME = p_tname
11 and cc.TABLE_NAME = c.TABLE_NAME
12 and c.CONSTRAINT_TYPE = 'P'
13 and cc.CONSTRAINT_NAME = c.CONSTRAINT_NAME
14 order by cc.position;
15 begin
16 l_create := 'create table aud$' || dbms_assert.SQL_OBJECT_NAME( p_tname ) ||
17 ' as select user username, sysdate del_date, cast( ''x'' as varchar2(30) ) tname ';
18 for x in C
19 loop
20 l_create := l_create || ', ' || x.c_name;
21 end loop;
22 l_create := l_create || ' from ' || p_tname || ' where 1=0';
23 dbms_output.put_line( l_create );
24 execute immediate l_create;
25
26 l_trigger := 'create or replace trigger aud$' ||
27 dbms_assert.SQL_OBJECT_NAME( p_tname ) || l_crlf ||
28 'after delete on ' || p_tname || l_crlf ||
29 'for each row ' || l_crlf ||
30 'begin ' || l_crlf ||
31 ' insert into aud$' || p_tname || '( username, del_date, tname ';
32 for x in C
33 loop
34 l_trigger := l_trigger || ', ' || x.c_name;
35 end loop;
36 l_trigger := l_trigger || ') values ' || l_crlf ||
37 '( USER, SYSDATE, ''' || p_tname || '''';
38 for x in C
39 loop
40 l_trigger := l_trigger || ', :old.' || x.c_name;
41 end loop;
42 l_trigger := l_trigger || ');' || l_crlf ||
43 'end;';
44
45 dbms_output.put_line( l_trigger );
46 execute immediate l_trigger;
47 end;
48 /
Procedure created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> exec generate_trigger( 'T1' );
create table aud$T1 as select user username, sysdate del_date, cast( 'x' as
varchar2(30) ) tname , X, Y from T1 where 1=0
create or replace trigger aud$T1
after delete on T1
for each row
begin
insert into aud$T1( username, del_date, tname , X, Y) values
( USER, SYSDATE,
'T1', :old.X, :old.Y);
end;
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> exec generate_trigger( 'T2' );
create table aud$T2 as select user username, sysdate del_date, cast( 'x' as
varchar2(30) ) tname , A from T2 where 1=0
create or replace trigger aud$T2
after delete on T2
for each row
begin
insert into aud$T2( username, del_date, tname , A) values
( USER, SYSDATE,
'T2', :old.A);
end;
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> exec generate_trigger( 'T3' );
create table aud$T3 as select user username, sysdate del_date, cast( 'x' as
varchar2(30) ) tname , C, A, B from T3 where 1=0
create or replace trigger aud$T3
after delete on T3
for each row
begin
insert into aud$T3( username, del_date, tname , C, A, B) values
( USER,
SYSDATE, 'T3', :old.C, :old.A, :old.B);
end;
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> delete from t1;
1 row deleted.
ops$tkyte%ORA11GR2> delete from t2;
1 row deleted.
ops$tkyte%ORA11GR2> delete from t3;
1 row deleted.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select * from aud$t1;
USERNAME DEL_DATE TNAME
------------------------------ --------- ------------------------------
X Y
---------- ----------
OPS$TKYTE 04-JAN-11 T1
1 2
ops$tkyte%ORA11GR2> select * from aud$t2;
USERNAME DEL_DATE TNAME
------------------------------ --------- ------------------------------
A
----------
OPS$TKYTE 04-JAN-11 T2
1
ops$tkyte%ORA11GR2> select * from aud$t3;
USERNAME DEL_DATE TNAME
------------------------------ --------- ------------------------------
C A B
---------- ---------- ----------
OPS$TKYTE 04-JAN-11 T3
3 1 2
Generic code always seems so cool - but it isn't (really - it usually isn't). Static code performs a lot better (in general) and often can be easily generated as demonstrated.
(I got lucky today - I actually typed that code in as you see it - it compiled the first time and worked :) Must be my lucky day)
If it does not do precisely what you want - it gives you the framework.
It also demonstrates one of the RARE uses of authid current_user. That lets this stored procedure run with the privileges of the INVOKER - including their roles (like create table, create trigger) and makes it so it can work for *any user* in their schema:
ops$tkyte%ORA11GR2> connect scott/tiger
Connected.
scott%ORA11GR2> exec ops$tkyte.generate_trigger( 'EMP' );
create table aud$EMP as select user username, sysdate del_date, cast( 'x' as
varchar2(30) ) tname , EMPNO from EMP where 1=0
create or replace trigger aud$EMP
after delete on EMP
for each row
begin
insert into aud$EMP( username, del_date, tname , EMPNO) values
( USER,
SYSDATE, 'EMP', :old.EMPNO);
end;
PL/SQL procedure successfully completed.