How to update to a column of another table automatically when doing an update using Trigger?

Question

CREATE TABLE TB_Empregado(
nome varchar(50),
rg int primary key, 
cpf varchar (11),
salario money
)
---

CREATE TABLE TB_Dependente(
rg_responsavel int constraint fk_TB_Dependente_Empregado references TB_Empregado(rg),
nome_dependente varchar (50),
data_nascimento date,
relacao varchar(45),
sexo char(1),
primary key (rg_responsavel, nome_dependente)
)

I need to create a TRIGGER that automatically updates the ID of the head of a dependent whenever it (employee ID) is updated in the employee table

Answer 1

You cannot update a foreign key like this, it would cause integrity problems, because if you update the RG of the Tb_employee table, the reference in the Tb_dependent table would no longer exist.

There are two ways to solve this:

1. Perform UPDATE in both tables using BEGIN TRANSACTION;
2. Recreate foreign key using "ON UPDATE CASCADE";

There is a question similar to yours on the global forum that might help you with some other later question.

question link