From: Jeff Kowalczyk <jtk@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: Trigger plpgsql function, how to test if OLD is set?
Date: Thu, 04 Dec 2003 13:18:02 -0500
Message-ID: <pan.2003.12.04.18.17.53.593118@yahoo.com> (raw)
I have a test I need to do in my trigger function to see if a standard set
of shipmentcharges exists, if not I insert two rows.
IF (SELECT COUNT(orderchargeid) FROM ordercharges WHERE
orderid=NEW.orderid OR orderid=OLD.orderid)=0 THEN
I added the "OR orderid=OLD.orderid" expression to handle the case where
the orderid is changed. A cascading update causes a duplicate set of
shipmentcharges to be added for the shipmentid, since the expression is
momentarily true.
When this trigger runs on INSERT operations, the OLD variable is not
yet set, and the trigger function returns an error.
Can anyone suggest a more sensible way to check for OLD before including
it in my expression, or another shortcut? Thanks.
-------------------------------------------------------------
CREATE OR REPLACE FUNCTION orders_initordercharges () RETURNS "trigger"
AS '
BEGIN
-- Check that no ordercharges exist for this orderid
IF (SELECT COUNT(orderchargeid) FROM ordercharges WHERE orderid=NEW.orderid OR orderid=OLD.orderid)=0 THEN
-- Insert standard initial set of ordercharges
INSERT INTO ordercharges (orderid, orderchargecode) VALUES (NEW.orderid,\'SALE\');
INSERT INTO ordercharges (orderid, orderchargecode) VALUES (NEW.orderid,\'S&H\');
END IF;
RETURN NEW;
END;
'
LANGUAGE plpgsql;
CREATE TRIGGER orders_initordercharges
BEFORE INSERT OR UPDATE ON orders
FOR EACH ROW
EXECUTE PROCEDURE orders_initordercharges ();
ALTER TABLE ONLY ordercharges
ADD CONSTRAINT if_order_exists FOREIGN KEY (orderid) REFERENCES
orders(orderid) ON UPDATE CASCADE ON DELETE CASCADE;
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: jtk@yahoo.com
Subject: Re: Trigger plpgsql function, how to test if OLD is set?
In-Reply-To: <pan.2003.12.04.18.17.53.593118@yahoo.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