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!
