Thread: trigger conversion advice needed
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;
/
When I was working on converting the trigger I noticed that Postgres doesn't
have the OF table_attribute ON syntax support. I just want the trigger to
fire when the order_date field in the order table is updated or inserted
like it is specified in the Oracle trigger specification above. So I did
this in Postgres:
CREATE OR REPLACE FUNCTION t_ship_date()
RETURNS TRIGGER AS ' BEGIN UPDATE orders SET ship_date = working_5days(new.order_date);
RETURN NEW; END; ' LANGUAGE 'plpgsql';
I justed used new.order_date in the Postgres one version. I'm pretty sure
that this isn't going to work, it will probably update every ship_date which
is not what I want. I just want to update the ship_date when the record's
order_date is updated. Is there some way in Postgres to specify a trigger to
fire only when a certain field in a table is changed or inserted (like I had
in the Oracle version)? I couldn't find anything for triggers to do that in
the docs so I hope someone can shed some light on this for me. I thought
maybe something like this could work too (probably closer to being correct):
CREATE OR REPLACE FUNCTION t_ship_date()
RETURNS TRIGGER AS ' BEGIN IF TG_OP = ''INSERT'' THEN UPDATE orders SET ship_date =
working_5days(new.order_date) where order_no = new.order_no; ELSIF TG_OP =''UPDATE'' THEN UPDATE
orders SET ship_date = working_5days(new.order_date) where order_no = old.order_no; END IF;
RETURN NEW; END; ' LANGUAGE 'plpgsql';
The working_5days function just adds 5 business days to a date fyi. Now the
second question I have is due to an error I keep getting when I try and make
the trigger definition below:
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. What am I doing wrong? If I
could get the trigger to compile then I could test it to see if it works the
same as the Oracle version. I wouldn't even have to ask the first question
if I could get it to compile but I thought I might as well ask anyway so I
don't have to post another message. Thanks for the help.
-Clint
P.S. 7.3.4 is the postgres version on the server.
_________________________________________________________________
Has one of the new viruses infected your computer? Find out with a FREE
online computer virus scan from McAfee. Take the FreeScan now!
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
"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
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
andI> 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>
SETship_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