How to make a change in "Cascade" using oracle

Question

How to make a change in "Cascade" using oracle

Navigation

#1 by (1 votes)

0

How can I change the ID of my table BANDAS_E_ARTISTAS and ensure that other foreign tables also change via PL / SQL?

TABLE BANDAS_E_ARTISTAS

ID NOME_ARTISTICO
01 RAÇA NEGRA
02 OS MENUDOS
03 KELLY KEY
04 LUIZ CARLOS
--------------------------

TABLE ARTISTA

ID NOME_REAL
03 KELLY JOANA
04 LUIZ CARLOS
--------------------------

TABLE BANDA

ID ESTILO
01 PAGODE
02 ROCK
--------------------------

TABLE ARTISTA_EM_BANDA

ID_BANDA ID_ARTISTA
01       04

--------------------------

The table BANDAS_E_ARTISTAS I store the ID (primary key) of the entire ID of the other tables.

sql oracle pl-sql

asked by anonymous 30.05.2017 / 14:58

1 answer

Array returns wrong value in java Subtract a value from a sum (SUM) in SQL?

score 1 · Accepted Answer

To do this automatically there is no way. You should create a procedure that receives the new PK and automatically updates on all referenced tables.

This procedure should do:

Create a new parent row with the new key value

Refresh all rows in the child table that point to the old parent row to point to the new parent row

Delete the old parent row

to eat

But if you want, there is an external library created by a programmer who does this automatically.

How to install is in this link

How to use:

In oracle SQLPlus, connected to the created database, run the following command for each table that will be upgraded from the PK:

Exec update_cascade.on_table(‘Nome da Tabela’);

Run the update command for the records you want to update:

UPDATE TABLE_X SET PRIMARYKEY_X = 1000 WHERE PRIMARYKEY_X = 10;

The result will be that where the system finds PK_X = 10, the trigger will switch to the child tables where the value is equal to 10 per 1000, without having to disable database constraint.