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;
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
