CODEWITHSUNDEEP
sqloracle-database
Lahkra
Lahkra

Reputation: 23

Oracle trigger syntax

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?

Upvotes: 0

Views: 130

Answers (2)

Mohsen Heydari
Mohsen Heydari

Reputation: 7284

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;

Upvotes: 1

smnbbrv
smnbbrv

Reputation: 24541

I guess this row

DEPT_ID = DEPT_ID - :NEW.DEPT_ID

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

Upvotes: 0

Related Questions