Update on Cascade Oracle with composite primary key

Asked

Viewed 145 times

0

create or replace TRIGGER cascade_update

    AFTER UPDATE OF project_name ON PROJECT
    FOR EACH ROW
    BEGIN

      UPDATE VEHICLE
       SET project_name = :new.project_name
       WHERE project_name = :old.project_name;

       UPDATE VELOCITY_LIMIT
       SET project_name = :new.project_name
       WHERE project_name = :old.project_name;

       UPDATE ENERGY
       SET project_name = :new.project_name
       WHERE project_name = :old.project_name;

       UPDATE GEAR
       SET project_name = :new.project_name
       WHERE project_name = :old.project_name;

       UPDATE THROTTLE
       SET project_name = :new.project_name
       WHERE project_name = :old.project_name;

       UPDATE REGIME
       SET project_name = :new.project_name
       WHERE project_name = :old.project_name;

       UPDATE NETWORK
       SET project_name = :new.project_name
       WHERE project_name = :old.project_name;

       UPDATE ROAD
       SET project_name = :new.project_name
       WHERE project_name = :old.project_name;

       UPDATE TOLL
       SET project_name = :new.project_name
       WHERE project_name = :old.project_name;

       UPDATE SECTION
       SET project_name = :new.project_name
       WHERE project_name = :old.project_name;

       UPDATE LOCAL
       SET project_name = :new.project_name
       WHERE project_name = :old.project_name;

    END;

.

create or replace PROCEDURE UPDATE_Project(old_name Project.project_name%type, project_name2 Project.project_name%type, project_description2  Project.project_description%type)
    IS

    BEGIN
    UPDATE PROJECT
    SET project_name = project_name2,
    project_description = project_description2
    WHERE project_name = old_name;

    END;

I need to update the field project_name which is in all tables, but when I run the trial I created presents these errors. I would like to know the best solution to update all fields.

SEVERE: ORA-02292: integrity restriction (FKROAD185752) violated - found child record

ORA-06512: on "CASCADE_UPDATE", line 27

ORA-04088: error during execution of Trigger 'CASCADE_UPDATE'

ORA-06512: in "UPDATE_PROJECT", row 5

ORA-06512: on line 1

  • 1º do SELECT * FROM USER_CONSTRAINTS WHERE CONSTRAINT_NAME = 'FKROAD185752' 2º In the definition of the PROJECT table there is probably a condition Cascade ( http://javaaberto.blogspot.com.br/2012/10/usando-cascade-no-bandados.html )

  • The result of the query was this https://image.prntscr.com/image/8256CX6CRWuN-eBji5dgkQ.png. I cannot enter the condition ON UPDATE ON DELETE in Oracle, always gives me error

  • In the description of the ROAD table what says this Constraint ?

  • The description of all Table Constraints Road are here https://image.prntscr.com/image/O36IzeAaQwqzCZ9bJ7yHBA.png - https://image.prntscr.com/image/73qNDTc1ScegaPn3qkJsQQ.png

  • Publish if possible the full description of the tables , look for in the tables involved a box that may be generating the error , I remember however that the tables were normalized the problem would not occur because project_name should be attribute only PROJECT

  • but I have project_name in all tables because project_name is part of a composite primary key, so when another table needs to be associated it receives the field project_name by default

  • It is already working. I chose to create another field for the primary key.

Show 2 more comments
No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.