pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Referencing external table in update/insert triggers
3+ messages / 2 participants
[nested] [flat]

* Referencing external table in update/insert triggers
@ 2010-02-17 15:18  Amitabh Kant <amitabhkant@gmail.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Amitabh Kant @ 2010-02-17 15:18 UTC (permalink / raw)
  To: 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

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Referencing external table in update/insert triggers
@ 2010-02-18 09:23  Richard Huxton <dev@archonet.com>
  parent: Amitabh Kant <amitabhkant@gmail.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Richard Huxton @ 2010-02-18 09:23 UTC (permalink / raw)
  To: Amitabh Kant <amitabhkant@gmail.com>; +Cc: pgsql-sql

On 17/02/10 15:18, Amitabh Kant wrote:
>
> 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;

> 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.

You can always do something like:
   SELECT t2.t2f4 INTO my_variable FROM t2 WHERE ...
   IF NEW.t1f4 > my_variable

However, for this case you can just do an update with an extended where 
clause:
   UPDATE t2 ... WHERE t2f1=NEW.d1 AND NEW.t1f4 > t2.t2f4

No need for the IF.

-- 
   Richard Huxton
   Archonet Ltd



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Referencing external table in update/insert triggers
@ 2010-02-18 09:33  Amitabh Kant <amitabhkant@gmail.com>
  parent: Richard Huxton <dev@archonet.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Amitabh Kant @ 2010-02-18 09:33 UTC (permalink / raw)
  To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql

On Thu, Feb 18, 2010 at 2:53 PM, Richard Huxton <dev@archonet.com> wrote:

> On 17/02/10 15:18, Amitabh Kant wrote:
>
>>
>> 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;
>>
>
>  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.
>>
>
> You can always do something like:
>  SELECT t2.t2f4 INTO my_variable FROM t2 WHERE ...
>  IF NEW.t1f4 > my_variable
>
> However, for this case you can just do an update with an extended where
> clause:
>  UPDATE t2 ... WHERE t2f1=NEW.d1 AND NEW.t1f4 > t2.t2f4
>
> No need for the IF.
>
> --
>  Richard Huxton
>  Archonet Ltd
>


Thanks Richard.

With regards

Amitabh Kant

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2010-02-18 09:33 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-02-17 15:18 Referencing external table in update/insert triggers Amitabh Kant <amitabhkant@gmail.com>
2010-02-18 09:23 ` Richard Huxton <dev@archonet.com>
2010-02-18 09:33   ` Amitabh Kant <amitabhkant@gmail.com>

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