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

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
    364 Posts
    ACCEPTED ANSWER

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

    ‏2012-02-10T09:41:07Z  in response to talalaevd
    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
      ACCEPTED ANSWER

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

      ‏2012-02-10T22:04:49Z  in response to andreasl
      Thanks!
      That's it :)