is roundly wrong! \r\n
Let's apply and see why this tip is stuck!
1. Let's create the table used:
Select all
create table tab_enderecos
( cliente_id number(10)
, endereco_id number(10)
, endereco varchar2(100)
);
2. From face, I have already seen that it will give a mistake in the trigger for 2 reasons:
(a). Syntax error in the variable statement. It should be:
(b). There is no exception. It will give an error if the table is empty or the first register inserted from the client_id ..
3. So let's insert the client 1 (to avoid the error).
Select all
SQL> insert into tab_enderecos values (1, 1, 'meu endereço');
1 row created.
SQL> COMMIT;
4. Now, apply the trigger - correcting the error of the statement I mentioned in item 3.
5. OK! Ready to test! The trigger promises that will put the automatic address_id !!! This is what we will see:
Select all
SQL> insert into tab_enderecos values (1, null, 'meu endereço');
1 row created.
SQL> select * from tab_enderecos;
CLIENTE_ID ENDERECO_ID ENDERECO
---------- ----------- ------------------------
1 1 meu endereço
1 2 meu endereço
2 row selected.
Ué? It worked? Yes, because the first was committed! If you run another insert without having commanded the previous one, it will be error. This is because
AutoNomous Transaction works in another session, that is, does not see what you just inserted. Therefore, this tip is fully stuck. See what happens if I insert a line without committing the previous:
Select all
SQL> insert into tab_enderecos values (1, null, 'meu endereço');
1 row created.
SQL> select * from tab_enderecos;
CLIENTE_ID ENDERECO_ID ENDERECO
---------- ----------- ------------------------
1 1 meu endereço
1 2 meu endereço
1 2 meu endereço
3 row selected.
Duplicated ID 2 !!!!! ERROR!!!
One must be very careful when using this type of transaction, because you may be making a great nonsense and detonating the information on your base. (as in the example quoted in the "tip" above)