By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE PARENTS (
ID NUMBER PRIMARY KEY,
TOTAL_AMOUNT NUMBER
);
CREATE TABLE CHILDREN (
ID NUMBER PRIMARY KEY,
PARENT_ID NUMBER REFERENCES PARENTS(ID),
AMOUNT NUMBER
);
BEGIN
INSERT INTO PARENTS( ID, TOTAL_AMOUNT ) VALUES ( 101, 10 );
INSERT INTO PARENTS( ID, TOTAL_AMOUNT ) VALUES ( 102, 15 );
INSERT INTO CHILDREN ( ID, PARENT_ID, AMOUNT ) VALUES ( 201, 101, 6 );
INSERT INTO CHILDREN ( ID, PARENT_ID, AMOUNT ) VALUES ( 202, 101, 4 );
INSERT INTO CHILDREN ( ID, PARENT_ID, AMOUNT ) VALUES ( 203, 102, 10 );
INSERT INTO CHILDREN ( ID, PARENT_ID, AMOUNT ) VALUES ( 204, 102, 5 );
COMMIT;
END;
/
1 rows affected
CREATE OR REPLACE TRIGGER TRG_TEST
AFTER UPDATE OF AMOUNT ON CHILDREN
BEGIN
UPDATE PARENTS p
SET TOTAL_AMOUNT = (
SELECT SUM(AMOUNT)
FROM CHILDREN c
WHERE c.PARENT_ID = p.ID
);
END;
/
UPDATE CHILDREN SET AMOUNT = 11 WHERE ID = 204;
1 rows affected
SELECT * FROM parents
| ID | TOTAL_AMOUNT |
|---|---|
| 101 | 10 |
| 102 | 21 |
ROLLBACK;
CREATE OR REPLACE TRIGGER TRG_TEST
FOR UPDATE OF AMOUNT ON CHILDREN
COMPOUND TRIGGER
TYPE ids_type IS TABLE OF PARENTS.ID%TYPE;
ids ids_type := ids_type();
AFTER EACH ROW
IS
BEGIN
IF :OLD.parent_id IS NOT NULL AND :OLD.parent_id NOT MEMBER OF ids THEN
ids.EXTEND;
ids(ids.COUNT) := :OLD.parent_id;
END IF;
IF :NEW.parent_id IS NOT NULL AND :NEW.parent_id NOT MEMBER OF ids THEN
ids.EXTEND;
ids(ids.COUNT) := :NEW.parent_id;
END IF;
END AFTER EACH ROW;
AFTER STATEMENT
IS
BEGIN
FORALL i IN 1 .. ids.COUNT
UPDATE parents
SET total_amount = (SELECT SUM(amount)
FROM children
WHERE parent_id = ids(i))
WHERE id = ids(i);
END AFTER STATEMENT;
END;
/
UPDATE CHILDREN SET AMOUNT = 11 WHERE ID = 204;
1 rows affected
SELECT * FROM parents
| ID | TOTAL_AMOUNT |
|---|---|
| 101 | 10 |
| 102 | 21 |