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!
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
