DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:from:date:message-id:subject:to:content-type;
	b=juWpI7JffWDLx4PtakLKRhVunlaFtq2x9E5qQewbBRorSrC1jsgkHlcov/RRQ+84M8
	D5mwQWFivCLZNsdWjbIHYVAI+435PTsHkF1ZAjlJMGbWnd3bzgtmHydwBa7v2bD9GX/l
	xdUuhCYnhUytp1+dp4gukZV2OMh/wrgR6buio=
MIME-Version: 1.0
From: Amitabh Kant <amitabhkant@gmail.com>
Date: Wed, 17 Feb 2010 20:48:27 +0530
Message-ID: <84b68b3d1002170718s5da61fam69de31ad1e54595f@mail.gmail.com>
Subject: Referencing external table in update/insert triggers
To: pgsql-sql@postgresql.org
Content-Type: multipart/alternative; boundary=0015175767cce5e9d3047fcd5d79

--0015175767cce5e9d3047fcd5d79
Content-Type: text/plain; charset=ISO-8859-1

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

--0015175767cce5e9d3047fcd5d79
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Hi<br><br>I have the following table structure for which I am trying to set=
 a AFTER INSERT or UPDATE trigger:<br><br>CREATE OR REPLACE FUNCTION update=
_data() RETURNS TRIGGER AS $update_data$<br>BEGIN<br>=A0=A0=A0 IF NEW.t1f4 =
&gt; t2.t2f4<br>

=A0=A0=A0 =A0=A0=A0 UPDATE t2 set t2f2=3DNEW.t1f2, t2f3=3DNEW.t1f3, t2f4=3D=
NEW.t1f4 where t2f1=3DNEW.d1;<br>=A0=A0=A0 =A0=A0=A0 RETURN NEW;<br>=A0=A0=
=A0 END IF;<br>END;<br>$update_data$ LANGUAGE plpgsql;<br><br>[t1]<br>t1f1 =
integer <br>t1f2 integer<br>t1f3 integer<br>

t1f4 timestamp without time zone<br>t1f5 character varying<br>t1f6 real<br>=
d1 bigint=A0 [fk: t2-&gt;t2f1]<br><br>CREATE TRIGGER test_trigger AFTER INS=
ERT OR UPDATE ON t1 FOR EACH ROW EXECUTE PROCEDURE update_data();<br><br>

[t2]<br>t2f1 integer NOT NULL<br>t2f2 integer<br>t2f3 integer<br>t2f4 times=
tamp without time zone<br><br>I would like to compare the date present in t=
he t2f4 with the new data being updated through the trigger.=A0 Using the i=
f 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.<br>

<br>With regards<br><br>Amitabh<br>

--0015175767cce5e9d3047fcd5d79--