1
Summarizing my problem, I need to automatically insert values into a table after other values are entered into it. The way I found to do this on the Oracle was by using a Trigger of the kind INSTEAD OF. However, after implementing and testing the Trigger, no value has been entered.
I built a small test case on the site dbfiddle, someone could help me and explain to me what exactly prevents me from doing mine Inserts?.
https://dbfiddle.uk/? rdbms=oracle_18&fiddle=262e83082d65b69aa8d851ac09b35795
Code I used for testing:
create table timdtl(dtlcodigo number, dtldescricao varchar(20));
create view dtl_view as select * from timdtl;
create or replace trigger gera_detalhamento
instead of insert on dtl_view
for each row
begin
insert into timdtl values(1, 'Trigger Test');
end;
/
insert into timdtl
values(666, 'Manual Insert');
Expected Result: 666 | Manual Insert 1 | Trigger Test
Result Obtained: 666 | Manual Insert
Note: I am using this specific Trigger to avoid the mutant table error that occurs when I try to insert values in the same table that is being updated.
I’ve never used this but I think Insert has to be in the view. "Instead of" inserting Trigger will be called. But I didn’t understand the original problem.
– Motta
The original problem is a little more complex and long to put all here, it would solve part of it. Even if I put the Insert in the view the result remains the same
– Felipe Andrioli
Look at this, similar to your case I believe. https://www.oracletutorial.com/plsql-tutorial/oracle-instead-of-triggers/
– Motta
I am exactly with this link open to understand better, but still could not solve my problem
– Felipe Andrioli