Re: Recursion in triggers?

On 24 Jan 2010, at 5:36, Gauthier, Dave wrote:

> Hi:
>
> I’m dealing with a hierarchical design where changes in one record can and should cause changes in other records
lowerinthe hierarchy.  I’m trying to use update triggers to do this.  And  recursion would be a real nice way to do
this.
>
> What I need to know is if, in the “after” update trigger I make the subsequent updates to other records in the same
table,with the OLD/NEW record ponters be set properly in those subsequent update trigger invocations?  Will the current
andmodified NEW.* values be passed down into the next update trigger “before” call as OLD.* values?  Or is recursion
likethis not allowed? 

I'm not really sure what you're trying to do, so it's a tad hard to answer.

Are you using multiple before-update triggers on the SAME table? In that case you ask an interesting question that I
don'tknow the answer to either. I do know that they'll fire ordered alphabetically on trigger name. 

A test case with a few raise notices is easily created though:

BEGIN;

CREATE FUNCTION x()
RETURNS trigger
AS $$
BEGIN
    RAISE NOTICE 'OLD.test = %, NEW.test = %', OLD.test, NEW.test;

    NEW.test := New.test + 1;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TABLE trigger_test(test int);
INSERT INTO trigger_test VALUES (1);

CREATE TRIGGER a BEFORE UPDATE ON trigger_test
    FOR EACH ROW EXECUTE PROCEDURE x();

CREATE TRIGGER b BEFORE UPDATE ON trigger_test
    FOR EACH ROW EXECUTE PROCEDURE x();

SET client_min_messages TO notice;

UPDATE trigger_test SET test=2 WHERE test=1;

ROLLBACK;

development=> \i /tmp/trigger_test.sql
BEGIN
CREATE FUNCTION
CREATE TABLE
INSERT 0 1
CREATE TRIGGER
CREATE TRIGGER
SET
psql:/tmp/trigger_test.sql:26: NOTICE:  OLD.test = 1, NEW.test = 2
psql:/tmp/trigger_test.sql:26: NOTICE:  OLD.test = 1, NEW.test = 3
UPDATE 1
ROLLBACK


Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4b5c183b10607129821012!