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.
Pinned topic Passing table row as a parameter from trigger. Is it possible?
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2012-02-10T22:04:49Z at 2012-02-10T22:04:49Z by talalaevd
andreasl 100000CHVU401 Posts
Re: Passing table row as a parameter from trigger. Is it possible?2012-02-10T09:41:07ZThis is the accepted answer. This is the accepted answer.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?