Trigger plpgsql function, how to test if OLD is set?

From: Jeff Kowalczyk <jtk(at)yahoo(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Trigger plpgsql function, how to test if OLD is set?
Date: 2003-12-04 18:18:02
Message-ID: pan.2003.12.04.18.17.53.593118@yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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;

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Tomasz Myrta 2003-12-04 18:26:08 Re: How do I convert an interval into integer?
Previous Message Wei Weng 2003-12-04 18:09:12 How do I convert an interval into integer?