Error on Trigger SQL Oracle

Question

Hi I'm having a problem while I'm creating a trigger on SQL Oracle.

My trigger is:

    create or replace trigger generatePassword
before insert on people
for each row
begin
    insert into people(nif,naame,date_birth,sex,adress,email,iban,password)
    values (:NEW.nif,:NEW.naame,:NEW.date_birth,:NEW.sex,:NEW.adress,:NEW.email,:NEW.iban,(select round(dbms_random.value(0000,9999)) from dual));
end;
/

The trigger was created successfully. Then, when I try to run the command:

insert into people (naame, date_birth, sex, adress, email, iban, nif, id) values ('Albert', '01-12-87', 'M', 'NY', '[email protected]', '000032134537512343231', '523456189', '70');

I get this error:

ORA-00036: maximum number of recursive SQL levels (50) exceeded ORA-06512: at "UTF8.GENERATEPASSWORD", line 2 ORA-04088: error during execution of trigger 'UTF8.GENERATEPASSWORD' ORA-06512: at "UTF8.GENERATEPASSWORD", line 2 ORA-04088: error during execution of trigger 'UTF8.GENERATEPASSWORD' ORA-06512: at "UTF8.GENERATEPASSWORD", line 2 ORA-04088: error during execution of trigger 'UTF8.GENERATEPASSWORD' ORA-06512: at "UTF8.GENERATEPASSWORD", line 2 ORA-04088: error during execution of trigger '

What is the problem here?

Possible duplicate of PLSQL - Iinsert in trigger causing recursive loop — fvu
What are you trying to accomplish? If a trigger on people tries to insert into people, you're going to get into an infinite loop (the initial insert fires the trigger which inserts a new row which fires the trigger again which inserts a new row which fires the trigger again...). If you are just trying to change the password value from the initial insert, you'd just want :new.password := round(dbms_random.value(0000,9999);. — Justin Cave
Justin, do you mean like this: create or replace trigger generatePassword before insert on people for each row begin :NEW.password = (select round(dbms_random.value(0000,9999)) from dual; insert into people(nif,naame,date_birth,sex,adress,email,iban,password) values (:NEW.nif,:NEW.naame,:NEW.date_birth,:NEW.sex,:NEW.adress,:NEW.email,:NEW.iban,:NEW.password ); end; / — Blackout

Alessandro Vecchio Alessandro Vecchio · Accepted Answer · 2016-05-22T10:35:35

As it has already been pointed out by Justin, you're code generates an infinite loop because the trigger fires for each insert statement included the one inside the trigger. A possible solution is this one:

CREATE OR REPLACE TRIGGER generatePassword
BEFORE INSERT ON people
FOR EACH ROW

BEGIN
:NEW.password := round(dbms_random.value(0000,9999);

END generatePassword;
/

Whenever the trigger fires, a password is generated and added to the original insert statement.

Error on Trigger SQL Oracle

1 Answers