To: pgsql-sql@postgresql.org
From: Jeff Kowalczyk <jtk@yahoo.com>
Subject: Trigger plpgsql function, how to test if OLD is set?
Date: Thu, 04 Dec 2003 13:18:02 -0500
Lines: 42
Message-ID: <pan.2003.12.04.18.17.53.593118@yahoo.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
User-Agent: Pan/0.14.2 (This is not a psychotic episode. It's a cleansing
	moment of clarity.)

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;