Hi,
I'm following this below link what you have suggested.
https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:59412348055 But the problem here is if i'm trying to run following code, its not working & throwing error.
=========================================================================================================
prompt create or replace trigger aud#&1
prompt after update on &1
prompt for each row
prompt begin
select ' audit_pkg.check_val( ''&1'', ''' || column_name ||
''', ' || ':new.' || column_name || ', :old.' ||
column_name || ');'
from user_tab_columns where table_name = upper('&1')
/
prompt end;;
prompt /
==============================================================================================================
I'm getting confused here what exactly its askig for.If you could help with simple example that would be great.Because i have not much Idea about Trigger.
Thanks
OK, store that bit in a script called "generate_trigger.sql", and then you call that in SQL Plus to build a trigger. Its a SQL that writes SQL if that makes sense.
eg
SQL> @c:\temp\generate.sql EMP
create or replace trigger aud#EMP
after update on EMP
for each row
begin
audit_pkg.check_val( 'EMP', 'EMPNO', :new.EMPNO, :old.EMPNO);
audit_pkg.check_val( 'EMP', 'ENAME', :new.ENAME, :old.ENAME);
audit_pkg.check_val( 'EMP', 'JOB', :new.JOB, :old.JOB);
audit_pkg.check_val( 'EMP', 'MGR', :new.MGR, :old.MGR);
audit_pkg.check_val( 'EMP', 'HIREDATE', :new.HIREDATE, :old.HIREDATE);
audit_pkg.check_val( 'EMP', 'SAL', :new.SAL, :old.SAL);
audit_pkg.check_val( 'EMP', 'COMM', :new.COMM, :old.COMM);
audit_pkg.check_val( 'EMP', 'DEPTNO', :new.DEPTNO, :old.DEPTNO);
end;
/
And assuming you've compiled the "audit_pkg" successfully, that trigger will be created and be ready to go. It saves you the effort of hand-coding each of the triggers.
Hope this helps.