[DATABASE] Consiglio su trigger ORACLE

[peppeweb]

Salve a tutti,

ho scritto un trigger per aggiornare una tabelle in funzione degli eventi che succedono su di un'altra tabella.

In sintesi:

Quando sulla tabella TB_TRANS_ARTICLES viene inserito, modificato o cancellato un record il trigger deve intercettare l'evento e aggiornare la tabella TB_POS_GIACENZE.

Io sono riuscito a fare soltanto sull'evento insert questo trigger:

CREATE OR REPLACE TRIGGER TriggerGIACENZE
AFTER INSERT
ON TB_TRANS_ARTICLES
FOR EACH ROW

DECLARE

ART TB_POS_GIACENZE%ROWTYPE;

BEGIN
SELECT * INTO ART
FROM TB_POS_GIACENZE
WHERE ARTICLE_ID = :NEW.ARTICLE_ID AND CASHREG_ID = (SELECT CASHREG_ID FROM TB_TRANSACTIONS WHERE ID = :NEW.TRANSACTION_ID);
IF :NEW.DELETE_OPERATOR_ID IS NULL THEN
UPDATE TB_POS_GIACENZE
SET QTA = QTA + :NEW.QTY_WEIGHT,
PRICE = PRICE + :NEW.PRICE
WHERE ARTICLE_ID = :NEW.ARTICLE_ID AND CASHREG_ID = (SELECT CASHREG_ID FROM TB_TRANSACTIONS WHERE ID = :NEW.TRANSACTION_ID);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
IF :NEW.DELETE_OPERATOR_ID IS NULL THEN
INSERT INTO TB_POS_GIACENZE ( ARTICLE_ID, CASHREG_ID, QTA, PRICE )
VALUES ( :NEW.ARTICLE_ID, (SELECT CASHREG_ID FROM TB_TRANSACTIONS WHERE ID = :NEW.TRANSACTION_ID), :NEW.QTY_WEIGHT, :NEW.PRICE );
END IF;
END;

[peppeweb]

Ho provato a modificarlo ma quando cancello un record sulla tabella TB_TRANS_ARTICLES mi da questo errore:

ORA-04091: table TB_TRANSACTIONS is mutating, trigger/funcion may not see it
ORA-06512: at TRIGGERGIACENZE"", line 6
ORA-04088: error during execution of trigger 'TRIGGERGIACENZE'


create or replace
TRIGGER TriggerGIACENZE
AFTER INSERT OR DELETE OR UPDATE
ON TB_TRANS_ARTICLES
FOR EACH ROW

DECLARE

ART TB_POS_GIACENZE%ROWTYPE;

BEGIN
SELECT * INTO ART
FROM TB_POS_GIACENZE
WHERE ARTICLE_ID = :NEW.ARTICLE_ID AND CASHREG_ID = (SELECT CASHREG_ID FROM TB_TRANSACTIONS WHERE ID = :NEW.TRANSACTION_ID);
IF :NEW.DELETE_OPERATOR_ID IS NULL THEN

IF INSERTING THEN
UPDATE TB_POS_GIACENZE SET QTA = QTA + :NEW.QTY_WEIGHT, PRICE = PRICE + :NEW.PRICE
WHERE ARTICLE_ID = :NEW.ARTICLE_ID AND CASHREG_ID = (SELECT CASHREG_ID FROM TB_TRANSACTIONS WHERE ID = :NEW.TRANSACTION_ID);
ELSIF UPDATING THEN
UPDATE TB_POS_GIACENZE SET QTA = QTA + :NEW.QTY_WEIGHT, PRICE = PRICE + :NEW.PRICE
WHERE ARTICLE_ID = :NEW.ARTICLE_ID AND CASHREG_ID = (SELECT CASHREG_ID FROM TB_TRANSACTIONS WHERE ID = :NEW.TRANSACTION_ID);
ELSE
UPDATE TB_POS_GIACENZE SET QTA = QTA - :NEW.QTY_WEIGHT, PRICE = PRICE - :NEW.PRICE
WHERE ARTICLE_ID = :NEW.ARTICLE_ID AND CASHREG_ID = (SELECT CASHREG_ID FROM TB_TRANSACTIONS WHERE ID = :NEW.TRANSACTION_ID);
END IF;

END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
IF :NEW.DELETE_OPERATOR_ID IS NULL THEN
INSERT INTO TB_POS_GIACENZE ( ARTICLE_ID, CASHREG_ID, QTA, PRICE )
VALUES ( :NEW.ARTICLE_ID, (SELECT CASHREG_ID FROM TB_TRANSACTIONS WHERE ID = :NEW.TRANSACTION_ID), :NEW.QTY_WEIGHT, :NEW.PRICE );
END IF;
END;

[peppeweb]

nessun sa aiutarmi ?