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

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;
 

Dnia 2003-12-04 19:18, Użytkownik Jeff Kowalczyk napisał:
> 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;

You can always check whether your trigger has been fired as insert or 
update trigger.

DECLARE  old_orderid  integer;
BEGIN  if TG_OP=''UPDATE'' then     old_orderid=OLD.orderid;  else     old_orderid=-1;  end if;
...

Regards,
Tomasz Myrta

Tomasz Myrta wrote:
> You can always check whether your trigger has been fired as insert or 
> update trigger.
> DECLARE old_orderid  integer;
> BEGIN
>    if TG_OP=''UPDATE'' then
>       old_orderid=OLD.orderid;
>    else
>       old_orderid=-1;
>    end if;

Thank you, that works well enough. I'm beginning to appreciate the
complexity of working with both the familiar DRI (contraints) and
triggers, which are something I have rarely used.