From: Clint Stotesbery <cstotes@hotmail.com>
To: pgsql-sql@postgresql.org
Subject: trigger conversion advice needed
Date: Wed, 26 Nov 2003 11:51:42 +0000
Message-ID: <BAY9-F11pKkVavr8nCy0001de1f@hotmail.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: cstotes@hotmail.com
Subject: Re: trigger conversion advice needed
In-Reply-To: <BAY9-F11pKkVavr8nCy0001de1f@hotmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox