Topic
  • 2 replies
  • Latest Post - ‏2012-02-10T22:04:49Z by talalaevd
talalaevd
talalaevd
2 Posts

Pinned topic Passing table row as a parameter from trigger. Is it possible?

‏2012-02-10T00:54:41Z |
I need to pass a table row to procedure from for each trigger.
Could I create an instance of row type based on a table that contains all data from current row and pass it as a parameter?
If yes then how?

Thanks a lot.
Updated on 2012-02-10T22:04:49Z at 2012-02-10T22:04:49Z by talalaevd
  • andreasl
    andreasl
    372 Posts

    Re: Passing table row as a parameter from trigger. Is it possible?

    ‏2012-02-10T09:41:07Z  
    If I'm getting you right, the 'referencing' and the 'with trigger references' clauses, both in trigger and in procedure/function definition, are what you want.

    Look at this example:

    
    create table trig_test (s serial primary key, i1 int, i2 
    
    int); insert into trig_test values (0, 0, 0);   create procedure trig_test_ut_proc() referencing old as old_row 
    
    new as new_row 
    
    for trig_test    -- <<<< let new_row.i2 = new_row.i1; end procedure;   create trigger trig_test_ut update of i1 on trig_test referencing old as old_row 
    
    new as new_row                  -- <<<< 
    
    for each row (execute procedure trig_test_ut_proc() with trigger references);                               -- <<<<   update trig_test set i1=1;   select * from trig_test;
    


    This way the complete table row is available to the triggered procedure without the need for passing anything.
    Is this what you needed?

    Cheers,
    Andreas
  • talalaevd
    talalaevd
    2 Posts

    Re: Passing table row as a parameter from trigger. Is it possible?

    ‏2012-02-10T22:04:49Z  
    • andreasl
    • ‏2012-02-10T09:41:07Z
    If I'm getting you right, the 'referencing' and the 'with trigger references' clauses, both in trigger and in procedure/function definition, are what you want.

    Look at this example:

    <pre class="jive-pre"> create table trig_test (s serial primary key, i1 int, i2 int); insert into trig_test values (0, 0, 0); create procedure trig_test_ut_proc() referencing old as old_row new as new_row for trig_test -- <<<< let new_row.i2 = new_row.i1; end procedure; create trigger trig_test_ut update of i1 on trig_test referencing old as old_row new as new_row -- <<<< for each row (execute procedure trig_test_ut_proc() with trigger references); -- <<<< update trig_test set i1=1; select * from trig_test; </pre>

    This way the complete table row is available to the triggered procedure without the need for passing anything.
    Is this what you needed?

    Cheers,
    Andreas
    Thanks!
    That's it :)