using :old and :new as parameters

herzal · Aug 13, 2009 6:43AM

Hi all,

is it possible that a trigger can call a procedure and use :old and :new as parameters? Kinda like this

create or replace trigger logTrigger after update on test_table for each row
begin
logProcedure(:old, :new)
end;

create or replace procedure logProcedure (oldRow in test_table%rowtype,newRow in test_table%rowtype) is
begin
-- do some stuff
end;

thanks for your help

21205 · Aug 13, 2009 6:45AM

Welcome to the forum!

You can't pass the complete row
you can pass individual column values, so it would be something like

create or replace trigger logTrigger after update on test_table for each row
begin
   logProcedure(:old.col1, :new.col1);
end;

21205 · Aug 13, 2009 6:45AM

Welcome to the forum!

You can't pass the complete row
you can pass individual column values, so it would be something like

create or replace trigger logTrigger after update on test_table for each row
begin
   logProcedure(:old.col1, :new.col1);
end;

21205 · Aug 13, 2009 6:47AM

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;

herzal · Aug 13, 2009 6:58AM

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)

21205 · Aug 13, 2009 7:03AM

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

21205 · Aug 13, 2009 7:05AM

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

herzal · Aug 13, 2009 7:20AM

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.

21205 · Aug 13, 2009 7:33AM

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

herzal · Aug 13, 2009 7:39AM

thanks, I will have a look

Forum Stats

Quick Links

Categories

using :old and :new as parameters

Best Answer

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

using :old and :new as parameters

Best Answer

Answers