pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTrigger plpgsql function, how to test if OLD is set?
3+ messages / 2 participants
[nested] [flat]
* Trigger plpgsql function, how to test if OLD is set?
@ 2003-12-04 18:18 Jeff Kowalczyk <jtk@yahoo.com>
0 siblings, 1 reply; 3+ messages in thread
From: Jeff Kowalczyk @ 2003-12-04 18:18 UTC (permalink / raw)
To: 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 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;
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Trigger plpgsql function, how to test if OLD is set?
@ 2003-12-04 18:36 Tomasz Myrta <jasiek@klaster.net>
parent: Jeff Kowalczyk <jtk@yahoo.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tomasz Myrta @ 2003-12-04 18:36 UTC (permalink / raw)
To: Jeff Kowalczyk <jtk@yahoo.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Trigger plpgsql function, how to test if OLD is set?
@ 2003-12-04 19:24 Jeff Kowalczyk <jtk@yahoo.com>
parent: Tomasz Myrta <jasiek@klaster.net>
0 siblings, 0 replies; 3+ messages in thread
From: Jeff Kowalczyk @ 2003-12-04 19:24 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-12-04 19:24 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-12-04 18:18 Trigger plpgsql function, how to test if OLD is set? Jeff Kowalczyk <jtk@yahoo.com>
2003-12-04 18:36 ` Tomasz Myrta <jasiek@klaster.net>
2003-12-04 19:24 ` Jeff Kowalczyk <jtk@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox