SQL & PL/SQL

or something like this

create or replace trigger logTrigger after update on test_table for each row
declare
   old_row test_table%rowtype;
   new_row test_table%rowtype;
begin
   old_row.col1 := :old.col1;
   ....
   new_row.col1 := :new.col1;

   logProcedure(old_row, new_row);
end;

wow, that was quick o_O

thanks for your reply.

I'll explain my problem: we have more than 300 tables and every insert/update/delete should be logged, so I need more than 300 triggers. I want to avoid that the trigger has to be rewritten every time the structure of the table changes. So I thouht I use a trigger and a log-procedure. The only things the trigger does are checking if the log-procedure is up2date, recreating the log-procedure if necessary, and calling the log-procedure with :old and :new as parameters.

But since that doesn't work: any suggestions how to solve that?

thanks for your help =) (sitting here with my stop watch)

Before writing your own implementation, check auditing.


sorry for the delay, but I had to run out do some groceries, feed the cat and debug some applications...
;)

Edited by: Alex Nuijten on Aug 13, 2009 1:03 PM

maybe something like http://download.oracle.com/docs/cd/B19306_01/network.102/b14266/cfgaudit.htm#sthref1769

thanks your your help, Alex =) but auditing is no option. I think I will end up using a DDL-trigger that recreates the log-triggers and then the log-triggers do the logging.

doing it with DDL triggers might proof quite tricky.
It would be pretty straight forward to write your own generator to create these triggers. Or take a look at codegen, a utility to generate source code.
http://codegen.inside.quest.com/index.jspa

thanks, I will have a look ;)