Reply-To: cstotesbery@acm.org
From: "Clint Stotesbery" <cstotes@hotmail.com>
To: tgl@sss.pgh.pa.us
Cc: pgsql-sql@postgresql.org
Subject: Re: trigger conversion advice needed
Date: Wed, 26 Nov 2003 15:56:30 +0000
Mime-Version: 1.0
Content-Type: text/plain; format=flowed
Message-ID: <BAY9-F41p68V8zDv60g0001fbb1@hotmail.com>

Thanks for the advice Tom. I figured out why my create trigger statement 
wouldn't compile. Postgres expects a FOR EACH ROW or FOR EACH STATEMENT 
clause before the EXECUTE portion. Oracle assumes a statement level trigger 
unless you specify it to be for each row. In addition Oracle allows for 
specific columns to be monitored for updates like I had in the Oracle 
trigger (AFTER UPDATE OR INSERT OF order_date ON orders).

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 <tgl@sss.pgh.pa.us>
To: cstotesbery@acm.org
CC: pgsql-sql@postgresql.org
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 
<cstotesbery@acm.org>; Wed, 26 Nov 2003 10:40:10 -0500
Received: from sss2.sss.pgh.pa.us (tgl@localhost [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: <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"
Message-ID: <15057.1069861208@sss.pgh.pa.us>
Return-Path: tgl@sss.pgh.pa.us
X-OriginalArrivalTime: 26 Nov 2003 15:40:10.0631 (UTC) 
FILETIME=[936E3170:01C3B433]

"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

_________________________________________________________________
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