Trigger plpgsql function, how to test if OLD is set?
От | Jeff Kowalczyk |
---|---|
Тема | Trigger plpgsql function, how to test if OLD is set? |
Дата | |
Msg-id | pan.2003.12.04.18.17.53.593118@yahoo.com обсуждение исходный текст |
Ответы |
Re: Trigger plpgsql function, how to test if OLD is set?
|
Список | 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 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;
В списке pgsql-sql по дате отправления: