From: Amitabh Kant <amitabhkant@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Referencing external table in update/insert triggers
Date: Wed, 17 Feb 2010 20:48:27 +0530
Message-ID: <84b68b3d1002170718s5da61fam69de31ad1e54595f@mail.gmail.com> (raw)
Hi
I have the following table structure for which I am trying to set a AFTER
INSERT or UPDATE trigger:
CREATE OR REPLACE FUNCTION update_data() RETURNS TRIGGER AS $update_data$
BEGIN
IF NEW.t1f4 > t2.t2f4
UPDATE t2 set t2f2=NEW.t1f2, t2f3=NEW.t1f3, t2f4=NEW.t1f4 where
t2f1=NEW.d1;
RETURN NEW;
END IF;
END;
$update_data$ LANGUAGE plpgsql;
[t1]
t1f1 integer
t1f2 integer
t1f3 integer
t1f4 timestamp without time zone
t1f5 character varying
t1f6 real
d1 bigint [fk: t2->t2f1]
CREATE TRIGGER test_trigger AFTER INSERT OR UPDATE ON t1 FOR EACH ROW
EXECUTE PROCEDURE update_data();
[t2]
t2f1 integer NOT NULL
t2f2 integer
t2f3 integer
t2f4 timestamp without time zone
I would like to compare the date present in the t2f4 with the new data being
updated through the trigger. Using the if line as listed above returns an
error. Is it possible to reference table t2 within the same trigger? I could
also use another trigger (BEFORE UPDATE) on t2 to achieve the same, but
would like to avoid it.
With regards
Amitabh
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: amitabhkant@gmail.com
Subject: Re: Referencing external table in update/insert triggers
In-Reply-To: <84b68b3d1002170718s5da61fam69de31ad1e54595f@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox