Message-ID: <3F71AD6F.35E644B0@rodos.fzk.de>
Date: Wed, 24 Sep 2003 16:42:56 +0200
From: Christoph Haller <ch@rodos.fzk.de>
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: [Fwd: plpgsql triggers question -> foo := NEW ?]
Content-Type: multipart/mixed; boundary="------------783C81E94DCE00AA5C2A5D35"

This is a multi-part message in MIME format.
--------------783C81E94DCE00AA5C2A5D35
Content-Type: text/plain; charset=iso-8859-2
Content-Transfer-Encoding: 7bit

Christoph Haller wrote:

> >
> > I've searched the interactive docs and found this link:
> >
> > http://www.postgresql.org/docs/7.3/interactive/plpgsql-trigger.html
> >
> > There is no answer to the question below:  How to set foo:= NEW or
> foo:=
> > OLD in plpgsql trigger function
> > (I have the same problem)
> >
> This should help:
>
> INSERT
> ---------
> DROP FUNCTION orderinsert() ;
>
> CREATE OR REPLACE FUNCTION orderinsert() RETURNS OPAQUE AS '
> BEGIN
>    IF NEW.CUSTOMER_ID ISNULL THEN
>       RAISE EXCEPTION "CUSTOMER_ID cannot be NULL value" ;
>    END IF ;
>    IF NEW.CUSTOMER_SESSION ISNULL THEN
>       RAISE EXCEPTION "CUSTOMER_SESSION cannot be NULL value" ;
>    END IF ;
>
>    INSERT INTO HEXORDERS ( CUSTOMER_ID, ORDER_KEY, DISTRIBUTOR_ID,
> ORDER_AMOUNT, ORDER_GST )
>    VALUES ( NEW.CUSTOMER_ID, NEW.CUSTOMER_SESSION, 1, 0, 0 ) ;
>
>    RETURN NEW ;
> END ;
> ' LANGUAGE 'plpgsql' ;
>
> DROP TRIGGER HEXCUST_TRIGGER1 ON HEXCUSTOMERS;
>
> CREATE TRIGGER HEXCUST_TRIGGER1
> AFTER INSERT ON HEXCUSTOMERS
> FOR EACH ROW EXECUTE PROCEDURE orderinsert() ;
>
> UPDATE
> ----------
> DROP FUNCTION orderupdate() ;
>
> CREATE OR REPLACE FUNCTION orderupdate() RETURNS OPAQUE AS '
> BEGIN
>    IF NEW.ORDER_ID ISNULL THEN
>       RAISE EXCEPTION ''ORDER_ID cannot be NULL value'' ;
>    END IF ;
>    IF NEW.CUSTOMER_ID ISNULL THEN
>       RAISE EXCEPTION ''CUSTOMER_ID cannot be NULL value'' ;
>    END IF ;
>
>    UPDATE HEXORDERS
>       SET ORDER_AMOUNT =
>
>                    SELECT SUM(CUSTITEM_QUANTITY * (ITEM_BASEPRICE +
> ((CUSTITEM_USERS - 1) * ITEM_USEPRICE)))
>                    FROM HEXCUSTITEMS, HEXITEMS
>                    WHERE HEXCUSTITEMS.CUSTOMER_ID = NEW.CUSTOMER_ID
>                    AND HEXCUSTITEMS.ORDER_ID = NEW.ORDER_ID
>                    AND HEXCUSTITEMS.ITEM_ID = HEXITEMS.ITEM_ID
>                    GROUP BY HEXCUSTITEMS.ORDER_ID,
> HEXCUSTITEMS.CUSTOMER_ID ),
>       ORDER_GST =
>
>                   SELECT SUM((CUSTITEM_QUANTITY * (ITEM_BASEPRICE +
> ((CUSTITEM_USERS - 1) * ITEM_USEPRICE))) * .1::numeric)
>                   FROM HEXCUSTITEMS, HEXITEMS
>                   WHERE HEXCUSTITEMS.CUSTOMER_ID = NEW.CUSTOMER_ID
>                   AND HEXCUSTITEMS.ORDER_ID = NEW.ORDER_ID
>                   AND HEXCUSTITEMS.ITEM_ID = HEXITEMS.ITEM_ID
>                   AND CUSTITEM_GST = TRUE
>                   GROUP BY HEXCUSTITEMS.ORDER_ID,
> HEXCUSTITEMS.CUSTOMER_ID )
>    WHERE ORDER_ID = NEW.ORDER_ID
>    AND CUSTOMER_ID = NEW.CUSTOMER_ID ;
>
>    RETURN NEW ;
> END ;
> ' LANGUAGE 'plpgsql' ;
>
> DROP TRIGGER HEXCUSTITEMS_TRIGGER1 ON HEXCUSTITEMS;
>
> CREATE TRIGGER HEXCUSTITEMS_TRIGGER1
> AFTER INSERT OR UPDATE ON HEXCUSTITEMS
> FOR EACH ROW EXECUTE PROCEDURE orderupdate() ;
>
> DROP TRIGGER HEXCUSTITEMS_TRIGGER3 ON HEXCUSTITEMS;
>
> CREATE TRIGGER HEXCUSTITEMS_TRIGGER3
> AFTER DELETE ON HEXCUSTITEMS
> FOR EACH ROW EXECUTE PROCEDURE orderupdate() ;
>
> Regards, Christoph

--------------783C81E94DCE00AA5C2A5D35
Content-Type: message/rfc822
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

X-Mozilla-Status2: 00000000
Message-ID: <3F6EB87B.6C0DD94E@rodos.fzk.de>
Date: Mon, 22 Sep 2003 10:53:15 +0200
From: Christoph Haller <ch@rodos.fzk.de>
X-Mailer: Mozilla 4.77 [en] (X11; U; HP-UX B.10.20 9000/780)
X-Accept-Language: en
MIME-Version: 1.0
To: news1@faith.digsys.bg
Subject: Re: [SQL] plpgsql triggers question -> foo := NEW ?
Content-Type: text/plain; charset=iso-8859-2
Content-Transfer-Encoding: 7bit

>
> I've searched the interactive docs and found this link:
>
> http://www.postgresql.org/docs/7.3/interactive/plpgsql-trigger.html
>
> There is no answer to the question below:  How to set foo:= NEW or
foo:=
> OLD in plpgsql trigger function
> (I have the same problem)
>
This should help:

INSERT
---------
DROP FUNCTION orderinsert() ;

CREATE OR REPLACE FUNCTION orderinsert() RETURNS OPAQUE AS '
BEGIN
   IF NEW.CUSTOMER_ID ISNULL THEN
      RAISE EXCEPTION "CUSTOMER_ID cannot be NULL value" ;
   END IF ;
   IF NEW.CUSTOMER_SESSION ISNULL THEN
      RAISE EXCEPTION "CUSTOMER_SESSION cannot be NULL value" ;
   END IF ;

   INSERT INTO HEXORDERS ( CUSTOMER_ID, ORDER_KEY, DISTRIBUTOR_ID,
ORDER_AMOUNT, ORDER_GST )
   VALUES ( NEW.CUSTOMER_ID, NEW.CUSTOMER_SESSION, 1, 0, 0 ) ;

   RETURN NEW ;
END ;
' LANGUAGE 'plpgsql' ;


DROP TRIGGER HEXCUST_TRIGGER1 ON HEXCUSTOMERS;

CREATE TRIGGER HEXCUST_TRIGGER1
AFTER INSERT ON HEXCUSTOMERS
FOR EACH ROW EXECUTE PROCEDURE orderinsert() ;


UPDATE
----------
DROP FUNCTION orderupdate() ;

CREATE OR REPLACE FUNCTION orderupdate() RETURNS OPAQUE AS '
BEGIN
   IF NEW.ORDER_ID ISNULL THEN
      RAISE EXCEPTION ''ORDER_ID cannot be NULL value'' ;
   END IF ;
   IF NEW.CUSTOMER_ID ISNULL THEN
      RAISE EXCEPTION ''CUSTOMER_ID cannot be NULL value'' ;
   END IF ;

   UPDATE HEXORDERS
      SET ORDER_AMOUNT =

                   SELECT SUM(CUSTITEM_QUANTITY * (ITEM_BASEPRICE +
((CUSTITEM_USERS - 1) * ITEM_USEPRICE)))
                   FROM HEXCUSTITEMS, HEXITEMS
                   WHERE HEXCUSTITEMS.CUSTOMER_ID = NEW.CUSTOMER_ID
                   AND HEXCUSTITEMS.ORDER_ID = NEW.ORDER_ID
                   AND HEXCUSTITEMS.ITEM_ID = HEXITEMS.ITEM_ID
                   GROUP BY HEXCUSTITEMS.ORDER_ID,
HEXCUSTITEMS.CUSTOMER_ID ),
      ORDER_GST =

                  SELECT SUM((CUSTITEM_QUANTITY * (ITEM_BASEPRICE +
((CUSTITEM_USERS - 1) * ITEM_USEPRICE))) * .1::numeric)
                  FROM HEXCUSTITEMS, HEXITEMS
                  WHERE HEXCUSTITEMS.CUSTOMER_ID = NEW.CUSTOMER_ID
                  AND HEXCUSTITEMS.ORDER_ID = NEW.ORDER_ID
                  AND HEXCUSTITEMS.ITEM_ID = HEXITEMS.ITEM_ID
                  AND CUSTITEM_GST = TRUE
                  GROUP BY HEXCUSTITEMS.ORDER_ID,
HEXCUSTITEMS.CUSTOMER_ID )
   WHERE ORDER_ID = NEW.ORDER_ID
   AND CUSTOMER_ID = NEW.CUSTOMER_ID ;

   RETURN NEW ;
END ;
' LANGUAGE 'plpgsql' ;

DROP TRIGGER HEXCUSTITEMS_TRIGGER1 ON HEXCUSTITEMS;

CREATE TRIGGER HEXCUSTITEMS_TRIGGER1
AFTER INSERT OR UPDATE ON HEXCUSTITEMS
FOR EACH ROW EXECUTE PROCEDURE orderupdate() ;

DROP TRIGGER HEXCUSTITEMS_TRIGGER3 ON HEXCUSTITEMS;

CREATE TRIGGER HEXCUSTITEMS_TRIGGER3
AFTER DELETE ON HEXCUSTITEMS
FOR EACH ROW EXECUTE PROCEDURE orderupdate() ;

Regards, Christoph


--------------783C81E94DCE00AA5C2A5D35--