Referencing external table in update/insert triggers

From: Amitabh Kant <amitabhkant(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Referencing external table in update/insert triggers
Date: 2010-02-17 15:18:27
Message-ID: 84b68b3d1002170718s5da61fam69de31ad1e54595f@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Tom Lane 2010-02-17 15:27:11 Re: very frustrating feature-bug
Previous Message Pavel Stehule 2010-02-17 11:37:36 Re: very frustrating feature-bug