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
>
> 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