0
Hello! I am starting in Java programming and also in Oracle SQL and I have a problem:
I need a particular record of a column of a table to be updated, added or removed from a column of another table. For example:
tabela1 -- nome
tabela2 -- identificador
when adding a name:
insert into tabela1 values ('usuario');
the name 'user' is also registered in table 2, in identifier;
when removing a name:
delete from tabela1 where nome = 'usuario';
delete also the same record in table2;
when updating a name:
update tabela1 set nome = 'usuario_2021' where nome = 'usuario';
same with table identifier.
so far, I’ve only been able to create a Trigger that adds the same record in both tables, but when I delete this record from Tabela1, the record remains in table2.
create table nome1 (
nome_1 varchar2(50));
create table nome2 (
nome_2 varchar2(50));
create or replace trigger trgg
after insert or update or delete on nome1
for each row
begin
INSERT INTO nome2(nome_2) VALUES(:new.nome_1);
end;
thanks for the help! hugs!
EDIT:
I managed to make a shot that works, but it doesn’t look good, it’s pretty ugly code, I think...
create or replace trigger trgg
after insert or update or delete on nome1
for each row
begin
if inserting then
insert into nome2(nome_2) values(:new.nome_1);
end if;
if updating then
delete from nome2 where nome_2 = nome_2;
insert into nome2(nome_2) values(:new.nome_1);
end if;
if deleting then
delete from nome2 where nome_2 = nome_2;
end if;
end;
IF INSERTING THEN ... UPDATING DELETING https://docs.oracle.com/cd/E17781_01/appdev.112/e18147/tdddg_triggers.htm#TDDDG52100
– Motta
The solution would be this, only delete,Insert could be an update.
– Motta