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 lower inthe 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 and modified NEW.* values be passed down into the next update trigger �before� call as OLD.* values? Or is recursion like this 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't know 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!

Gauthier, Dave wrote:

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?

They'll be set properly. I'm not sure they'll be set how you
want/expect, though.

Will the current and modified NEW.* values be passed down
into the next update trigger “before” call as OLD.* values?

Eh? Why would they? Your AFTER trigger when fired modifies a different
record, right, rather than modifying the same one again. So why would
the `NEW' and `OLD' variables in the second firing have anything to do
with those in the first firing?

(Or have I misunderstood what you're trying to ask?)

Is your question really one about when the changes become visible? Ie,
if an AFTER UPDATE ... FOR EACH ROW trigger performs an UPDATE on a
table, does the second instance of that same trigger procedure see the
changes made to the first row?

That I can't give you a guaranteed-accurate answer to right now (though
"I think so") ... but suggest that writing a simple test case would be a
a pretty conclusive way to find out if the docs are unclear.

--
Craig Ringer

Ya, I worded the original poorley. Let me try again....

The after update trigger on the table sets some of the NEW.column values for record A. Then it executes another update on the same table, but on record B. That second execution of the update trigger needs to see the mods made to record A.

One table being updated, calling more updates in the after trigger, needs to see the mods made in the previous update trigger executions.

It's like a cascading operation, but on just one table.

Sorry about the confussion, and thanks for the help.

-----Original Message-----
From: Craig Ringer [mailto:craig@postnewspapers.com.au]
Sent: Sunday, January 24, 2010 11:34 AM
To: Gauthier, Dave
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Recursion in triggers?

Gauthier, Dave wrote:

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?

They'll be set properly. I'm not sure they'll be set how you
want/expect, though.

Will the current and modified NEW.* values be passed down
into the next update trigger "before" call as OLD.* values?

Eh? Why would they? Your AFTER trigger when fired modifies a different
record, right, rather than modifying the same one again. So why would
the `NEW' and `OLD' variables in the second firing have anything to do
with those in the first firing?

(Or have I misunderstood what you're trying to ask?)

Is your question really one about when the changes become visible? Ie,
if an AFTER UPDATE ... FOR EACH ROW trigger performs an UPDATE on a
table, does the second instance of that same trigger procedure see the
changes made to the first row?

That I can't give you a guaranteed-accurate answer to right now (though
"I think so") ... but suggest that writing a simple test case would be a
a pretty conclusive way to find out if the docs are unclear.

--
Craig Ringer

"Gauthier, Dave" <dave.gauthier@intel.com> writes:

Ya, I worded the original poorley. Let me try again....
The after update trigger on the table sets some of the NEW.column values for record A. Then it executes another update on the same table, but on record B. That second execution of the update trigger needs to see the mods made to record A.

Changing NEW in an after trigger has no effect outside the trigger
function itself. It's too late to affect the data that went into the
table --- that's more or less the whole point of AFTER vs BEFORE
triggers.

However, once you get that issue straightened out, it is true that
triggers fired pursuant to the UPDATE inside the first trigger will
also see whatever data changes the first trigger saw. See
http://www.postgresql.org/docs/8.4/static/trigger-datachanges.html

regards, tom lane