trigger conversion advice needed - Mailing list pgsql-sql
From | Clint Stotesbery |
---|---|
Subject | trigger conversion advice needed |
Date | |
Msg-id | BAY9-F11pKkVavr8nCy0001de1f@hotmail.com Whole thread Raw |
Responses |
Re: trigger conversion advice needed
|
List | pgsql-sql |
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