Wednesday, December 16, 2009

Oracle trigger question

create or replace trigger trig1
before insert on emp1
for each row
begin
insert into emp1(empid) values (seq1.nextval);
end;
OR
create or replace trigger trig2
before insert on emp1
for each row
begin
select seq1.nextval into :new.empid from dual;
end;
/

Answer : Second one.
First one will result in this :
SQL> insert into emp1 (empid) values (1);
insert into emp1 (empid) values (1)
*
ERROR at line 1:
ORA-00036: maximum number of recursive SQL levels (50) exceeded
ORA-06512: at "SCOTT.TRIG1", line 2
ORA-04088: error during execution of trigger 'SCOTT.TRIG1'
ORA-06512: at "SCOTT.TRIG1", line 2
ORA-04088: error during execution of trigger 'SCOTT.TRIG1'
ORA-06512: at "SCOTT.TRIG1", line 2
ORA-04088: error during execution of trigger 'SCOTT.TRIG1'
ORA-06512: at "SCOTT.TRIG1", line 2
ORA-04088: error during execution of trigger 'SCOTT.TRIG1'
ORA-06512: at "SCOTT.TRIG1", line 2
ORA-04088: error during execution of trigger 'SCOTT.TRIG1'
ORA-06512: at "SCOTT.TRIG1", line 2
ORA-04088: error during execution of trigger 'SCOTT.TRIG1'
ORA-06512: at "SCOTT.TRIG1", line 2
ORA-04088: error during execution of trigger 'SCOTT.TRIG1'
ORA-06512: at "SCOTT.TRIG1", line 2
ORA-04088: error during execution of trigger 'SCOTT.TRIG1'
ORA-06512: at "SCOTT.TRIG1", line 2
ORA-04088: error during execution of trigger 'SCOTT.TRIG1'
ORA-06512: at "SCOTT.TRIG1", line 2
ORA-04088: error during execution of trigger 'SCOTT.TRIG1'
ORA-06512

No comments:

Blog Archive