Forum Stats

  • 3,851,566 Users
  • 2,263,999 Discussions
  • 7,904,781 Comments

Discussions

using :old and :new as parameters

herzal
herzal Member Posts: 26
edited Aug 13, 2009 7:39AM in SQL & PL/SQL
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 =)
Tagged:

Best Answer

  • 21205
    21205 Member Posts: 6,168 Gold Trophy
    Answer ✓
    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;

Answers

  • 21205
    21205 Member Posts: 6,168 Gold Trophy
    Answer ✓
    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
    21205 Member Posts: 6,168 Gold Trophy
    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
    herzal Member Posts: 26
    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
    21205 Member Posts: 6,168 Gold Trophy
    edited Aug 13, 2009 7:04AM
    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
    21205 Member Posts: 6,168 Gold Trophy
    maybe something like http://download.oracle.com/docs/cd/B19306_01/network.102/b14266/cfgaudit.htm#sthref1769
  • herzal
    herzal Member Posts: 26
    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
    21205 Member Posts: 6,168 Gold Trophy
    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
    herzal Member Posts: 26
    thanks, I will have a look ;)
This discussion has been closed.