"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
