To: cstotesbery@acm.org
Cc: pgsql-sql@postgresql.org
Subject: Re: trigger conversion advice needed 
In-reply-to: <BAY9-F11pKkVavr8nCy0001de1f@hotmail.com> 
References: <BAY9-F11pKkVavr8nCy0001de1f@hotmail.com>
Comments: In-reply-to "Clint Stotesbery" <cstotes@hotmail.com>
	message dated "Wed, 26 Nov 2003 11:51:42 +0000"
Date: Wed, 26 Nov 2003 10:40:08 -0500
Message-ID: <15057.1069861208@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Clint Stotesbery" <cstotes@hotmail.com> writes:
> I'm working on converting a simple trigger from Oracle to Postgres and I 
> have a couple ofl questions that I need some help on please. First here's 
> the Oracle trigger:

> CREATE OR REPLACE TRIGGER t_ship_date
> AFTER UPDATE OR INSERT OF order_date ON orders
> BEGIN
>    UPDATE orders
>    SET ship_date = working_5days(order_date);
> END;

It looks to me like this trigger implicitly assumes that an UPDATE
command would only affect the row it was fired for --- which is not at
all how Postgres will interpret such a command.

(Alternatively, maybe the trigger actually does result in recomputing
every row's ship_date?  You would only notice if ship_date had been
changed manually in some rows to be different from order_date + 5...)

Guessing at what is actually wanted here, my inclination would be to use
a BEFORE INSERT OR UPDATE trigger and to detect updates by change from
OLD to NEW.  The INSERT case would simply do

	NEW.ship_date := working_5days(NEW.order_date);
	RETURN NEW;

The UPDATE case would look like

	IF NEW.order_date <> OLD.order_date THEN
	    NEW.ship_date := working_5days(NEW.order_date);
	END IF;
	RETURN NEW;

Pretty simple when you get the hang of it.

> CREATE TRIGGER t_ship_date AFTER UPDATE OR INSERT ON orders
> EXECUTE PROCEDURE t_ship_date();

> I always get a parse error at or near execute.

You need to say FOR EACH ROW in there too.

			regards, tom lane