Hi Tom,

I had asked a similar question earlier this month. For some reason, it is not letting me reply or update the same thread. Hence opening a new one.

I wanted to provide additional details on my question and request your help.

The requirement is to create a new oracle form to list a few tables & their columns. The user will chose the table and columns to audit. Based on their selection, a trigger has to be created dynamically. once created, the trigger will not change at least for an year. There will be only one user who would be authorized to chose the tables and columns to audit.

Have created a static trigger.
---------------------------------------------------------------------------------------
create or replace trigger TRIG_TEST_08052016
after insert or update or delete
on ARUN.trig_test
for each row

declare

log_id_v varchar2(10);
user_v varchar2(25);
change_type_v varchar2(2) ;
last_updated_by_v varchar2(25);
change_details_v varchar2(150);

-- Call this trigger from a procedure instead,
-- Not a good practise to use PRAGMA, Commit


-- PRAGMA AUTONOMOUS_TRANSACTION ;
/*

old_user_id ||'~'||new_user_id ||'~'||created_by ||'~'||old_test_num ||'~'||new_test_num ||'~'||
old_first_name ||'~'||new_first_name ||'~'||old_last_name ||'~'||new_last_name ||'~'||old_start_date ||'~'||
new_start_date ||'~'||old_end_date ||'~'||new_end_date ||'~'||old_salary ||'~'||new_salary ||'~'||
old_description ||'~'||new_description ||'~'||old_created_by ||'~'||new_created_by ||'~'||old_create_date||'~'||
current_date_time

*/


begin

dbms_output.put_line ('At 1');

select user into user_v from dual;

dbms_output.put_line('User: ' ||user_v) ;


if inserting then
dbms_output.put_line ('At 2');
change_type_v := 'I' ;

select user into user_v from dual;
last_updated_by_v := user_v;

change_details_v:= ' ' ||'~'||:new.user_id ||'~'||user_v ||'~'|| ' ' ||'~'|| :new.test_num ||'~'||
' ' ||'~'||:new.first_name ||'~'|| ' ' ||'~'|| :new.last_name ||'~'|| ' ' ||'~'||
:new.start_date ||'~'|| ' ' ||'~'||:new.end_date ||'~'|| ' ' ||'~'||:new.salary ||'~'||
' ' ||'~'||:new.description ||'~'|| ' ' ||'~'||:new.created_by ||'~'|| ' ' ||'~'||
to_char(sysdate,'DD-MON-YYYY HH24:MI:SS' ) ;

elsif updating then
dbms_output.put_line ('At 3');
change_type_v := 'U' ;
last_updated_by_v := :new.last_updated_by;

change_details_v:= :old.user_id ||'~'||:new.user_id ||'~'||user_v ||'~'||:old.test_num ||'~'||:new.test_num ||'~'||
:old.first_name ||'~'||:new.first_name ||'~'||:old.last_name ||'~'||:new.last_name ||'~'||:old.start_date ||'~'||
:new.start_date ||'~'||:old.end_date ||'~'||:new.end_date ||'~'||:old.salary ||'~'||:new.salary ||'~'||
:old.description||'~'||:new.description ||'~'||:old.created_by ||'~'||:new.created_by ||'~'||:old.create_date||'~'||
to_char(sysdate,'DD-MON-YYYY HH24:MI:SS' ) ;


elsif deleting then
change_type_v := 'D' ;
last_updated_by_v := :old.last_updated_by;

change_details_v:= :old.user_id ||'~'|| ' ' ||'~'||user_v ||'~'||:old.test_num ||'~'|| ' ' ||'~'||
:old.first_name ||'~'|| ' ' ||'~'||:old.last_name ||'~'|| ' ' ||'~'||:old.start_date ||'~'||
' ' ||'~'||:old.end_date ||'~'|| ' ' ||'~'||:old.salary ||'~'|| ' ' ||'~'||
:old.description||'~'|| ' ' ||'~'||:old.created_by ||'~'|| ' ' ||'~'||:old.create_date||'~'||
to_char(sysdate,'DD-MON-YYYY HH24:MI:SS' ) ;



end if;


-- :new.create_date := to_char(sysdate,'DD-MON-YYYY HH24:MI:SS' ) ;
-- :new.created_by := user_v ;

dbms_output.put_line ('At 4');


dbms_output.put_line ('At 5');

-- change_details_v := 'Arun'; --- ||'~'||'Tom' ; ---||'~'||:old.salary||'~'||:new.salary;

dbms_output.put_line ('At 6');
dbms_output.put_line ('change_type_v :' ||change_type_v);
dbms_output.put_line ('last_updated_by :' ||last_updated_by_v);
dbms_output.put_line ('change_details_v :' ||change_details_v);


dbms_output.put_line ('At 7');

select trig_log_logid.nextval
into log_id_v
from dual;

dbms_output.put_line ('At 8');

insert into trig_log_all_tables
(log_id,
dml_action,
updated_by,
details)
values
( log_id_v ,
change_type_v ,
last_updated_by_v ,
change_details_v
) ;

dbms_output.put_line ('At 9');

-- commit;

exception

when others then dbms_output.put_line ('Error: ' || SQLCODE ||'-'||Substr(SQLERRM,1,50));

end;
---------------------------------------------------------------------------------------------------

Test the trigger using :

BEGIN
insert_rows('trig_test', ' ''28'', ''56'',''Sam'',''Adams'',sysdate,sysdate,9001,''Testing'',''Sam'',''Sam'',sysdate');
commit;
END;
---------------------------------------------------------------------------------------------------

Could you please help.

I need your advise on how to pass the table & columns selected by the user to the dynamic trigger script. Also, if you could point me to any articles or books on this, I would appreciate it. I have tried researching online and have not found any articles that help.

Thank you

create or replace procedure p ( col_list dbms_sql.varchar2_table ) is
  create_trig varchar2(4000);
begin
  create_trig := 'create or replace trigger trg
after insert or update or delete
on ARUN.trig_test
for each row 
begin

  insert into trig_test_aud (';
  
  for i in col_list.first .. col_list.last loop
    create_trig := create_trig || col_list(i) || ',';
  end loop;
  
  --remove trailling comma
  create_trig := substr(create_trig, 1, length(create_trig)-1) || 
    ') values (';
  
  for i in col_list.first .. col_list.last loop
    create_trig := create_trig || ':new.' || col_list(i) || ',';
  end loop;
  
  create_trig := substr(create_trig, 1, length(create_trig)-1) || 
  ');
end;';

  execute immediate create_trig;

end p;
/