Re: Referencing external table in update/insert triggers

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