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 WHEREorderid=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.orderidOR 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
UPDATECASCADE ON DELETE CASCADE;