Oracle trigger syntax

Question

I have a question about my trigger that I'm trying to create between two tables. When one table is updated the other should be updated too, but I seem to be missing proper syntax.

CREATE OR REPLACE TRIGGER TRIG_DEPT_ONUPDATE
AFTER UPDATE OF DEPT_ID ON DEPARTMENT FOR EACH ROW
BEGIN
    UPDATE TEAM
        SET DEPT_ID = :NEW.DEPT_ID
        WHERE TEAM.DEPT_ID = :NEW.DEPT_ID;
END;
/

I get errors on update ("integrity constraint (%s.%s) violated - child record found"), but using the code:

CREATE OR REPLACE TRIGGER TRIG_DEPT_ONUPDATE
AFTER UPDATE OF DEPT_ID ON DEPARTMENT FOR EACH ROW
BEGIN
    UPDATE TEAM
        SET DEPT_ID = :NEW.DEPT_ID;
END;
/

it changes every single row after an update, though only select few need the change. Should an If statement be worked in somehow?

There is no Oracle 4.0.0.13

user330315
– user330315

2014-03-06 16:18:48 +00:00
Commented Mar 6, 2014 at 16:18 — user330315
– user330315, Commented Mar 6, 2014 at 16:18

Mohsen Heydari · Accepted Answer · 2014-03-06 19:43:21Z

1

To access the newly updated row values, you need a row level trigger not a statement level trigger:

CREATE OR REPLACE TRIGGER TRIG_DEPT_ONUPDATE
AFTER UPDATE OF DEPT_ID ON TEAM
for each row
BEGIN
    UPDATE DEPARTMENT
    SET DEPT_ID = :NEW.DEPT_ID
    Where DEPT_ID = :OLD.DEPT_ID;
END;

edited Mar 6, 2014 at 19:43

answered Mar 6, 2014 at 16:16

Mohsen Heydari

7,2924 gold badges33 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lahkra Over a year ago

Oh, that changed all of my rows, but not the one needed, when using 'For Each Row'.

Lahkra Over a year ago

Thanks for the help, I still got the ("integrity constraint (%s.%s) violated - child record found") upon update, but then I just updated the other table and it worked out fine!

smnbbrv · Accepted Answer · 2014-03-06 17:01:19Z

0

I guess this row

DEPT_ID = DEPT_ID - :NEW.DEPT_ID

generates some DEPT_ID which is not existing. That's the error reason.

answered Mar 6, 2014 at 17:01

smnbbrv

24.7k9 gold badges85 silver badges115 bronze badges

Collectives™ on Stack Overflow

Oracle trigger syntax

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related