Well I ended up adding FOR EACH STATEMENT to the Postgres version after the first email. I got a message back that said statement triggers weren't implemented in Postgres yet even though according to the 7.3 docs it can be used. I looked at the changelog for 7.4 and it said:
"Add statement-level triggers (Neil)
While this allows a trigger to fire at the end of a statement, it does not allow the trigger to access all rows modified by the statement. This capability is planned for a future release."
I wonder if that means that I can specify FOR EACH STATEMENT and have it compile fine but it seems like that I can't acccess new and old though still in 7.4. I'll have to experiment with what you suggested and perhaps look into upgrading to 7.4 as well.
Thanks,
Clint
----Original Message Follows----
From: Tom Lane <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
CC: [EMAIL PROTECTED]
Subject: Re: [SQL] trigger conversion advice needed
Date: Wed, 26 Nov 2003 10:40:08 -0500
Received: from alias2.acm.org ([199.222.69.92]) by mc11-f24.hotmail.com with Microsoft SMTPSVC(5.0.2195.6713); Wed, 26 Nov 2003 07:40:10 -0800
Received: from sss.pgh.pa.us ([192.204.191.242]) by alias2.acm.org (ACM Email Forwarding Service) with ESMTP id CRY73883 for <[EMAIL PROTECTED]>; Wed, 26 Nov 2003 10:40:10 -0500
Received: from sss2.sss.pgh.pa.us ([EMAIL PROTECTED] [127.0.0.1])by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id hAQFe819015058;Wed, 26 Nov 2003 10:40:08 -0500 (EST)
X-Message-Info: JGTYoYF78jE74k1WFZAS8n73gEHv7D0r
In-reply-to: <[EMAIL PROTECTED]>
References: <[EMAIL PROTECTED]>
Comments: In-reply-to "Clint Stotesbery" <[EMAIL PROTECTED]>message dated "Wed, 26 Nov 2003 11:51:42 +0000"
Message-ID: <[EMAIL PROTECTED]>
Return-Path: [EMAIL PROTECTED]
X-OriginalArrivalTime: 26 Nov 2003 15:40:10.0631 (UTC) FILETIME=[936E3170:01C3B433]
"Clint Stotesbery" <[EMAIL PROTECTED]> 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
_________________________________________________________________
Set yourself up for fun at home! Get tips on home entertainment equipment, video game reviews, and more here. http://special.msn.com/home/homeent.armx
---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
