CODEWITHSUNDEEP
oracle-databasetriggers
user918477
user918477

Reputation:

maximum number of recursive SQL levels

    java.sql.SQLException: ORA-00036: maximum number of recursive SQL levels (50) exceeded ORA-06512: at "EPOLICIA.EMER_COMPLAINT_VALIDATE", 
line 5 ORA-04088: error during execution of trigger 'EPOLICIA.EMER_COMPLAINT_VALIDATE' ORA-06512: at "EPOLICIA.EMER_COMPLAINT_VALIDATE", 
line 5 ORA-04088: error during execution of trigger 'EPOLICIA.EMER_COMPLAINT_VALIDATE' ORA-06512: at "EPOLICIA.EMER_COMPLAINT_VALIDATE", 
line 5 ORA-04088: error during execution of trigger 'EPOLICIA.EMER_COMPLAINT_VALIDATE' ORA-06512: at "EPOLICIA.EMER_COMPLAINT_VALIDATE", 
line 5 ORA-04088: error during execution of trigger 'EPOLICIA.EMER_COMPLAINT_VALIDATE' ORA-06512: at "EPOLICIA.EMER_COMPLAINT_VALIDATE", 
line 5 ORA-04088: error during execution of trigger 'EPOLICIA.EMER_COMPLAINT_VALIDATE' ORA-06512: at "EPOLICIA.EMER_COMPLAINT_VALIDATE", 
line 5 ORA-04088: error during execution of trigger 'EPOLICIA.EMER_COMPLAINT_VALIDATE' ORA-06512: at "EPOLICIA.EMER_COMPLAINT_VALIDATE", 
line 5 ORA-04088: error during execution of trigger 'EPOLICIA.EMER_COMPLAINT_VALIDATE' ORA-06512: at "EPOLICIA.EMER_COMPLAINT_VALIDATE", 
line 5 ORA-04088: error during execution of trigger 'EPOLICIA.EMER_COMPLAINT_VALIDATE' ORA-06512: at "EPOLICIA.EMER_COMPLAINT_VALIDATE", 
line 5 ORA-04088: error during execution of trigger 'EPOLICIA.EMER_COMPLAINT_VALIDATE' ORA-06512: at "EPOLICIA.EMER_COMPLAINT_VALIDATE", 
line 5 ORA-04088: error during execution of trigger 'EPOLICIA.EMER_COMPLAINT_VALIDATE' ORA-06512: at "EPOLICIA.EMER_COMPLAINT_VALIDATE", 
line 5 ORA-04088: error during execution of trigger 'EPOLICIA.EMER_COMPLAINT_VALIDATE' ORA-06512: at "EPOLICIA.EMER_COMPLAINT_VALIDATE", 
line 5 ORA-04088: error during execution of trigger 'EPOLICIA.EMER_COMPLAINT_VALIDATE' ORA-06512: at "EPOLICIA.EMER_COMPLAINT_VALIDATE", 
line 5 ORA-04088: error during execution of trigger 'EPOLICIA.EMER_COMPLAINT_VALIDATE' ORA-06512: at "EPOLICIA.EMER_COMPLAINT_VALIDATE", 
line 5 ORA-04088: error during execution of trigger 'EPOLICIA.EMER_COMPLAINT_VALIDATE' ORA-06512: at "EPOLICIA.EMER_COMPLAINT_VALIDATE", 
line 5 ORA-04

I've created one trigger, by which i will restrict the user to accept only one complaint from a particular ip address in a single day.

EMER_COMPLAINT_VALIDATE 

create or replace trigger emer_complaint_validate before insert on emer_complaint for each row
begin
if(((:new.date_time) = (:old.date_time)) AND ((:new.ip_add) = (:old.ip_add))) then
RAISE_APPLICATION_ERROR(-20001, 'Only one complaint accepted !!'); 
ELSE
insert into emer_complaint values(:new.case_id,:new.complaint,:new.land_mark,:new.station_id,:new.date_time,:new.ip_add,:new.status);
end if;
end;
/

structure of table, on which trigger has applied EMER_COMPLAINT

Upvotes: 0

Views: 1656

Answers (1)

Vincent Malgrat
Vincent Malgrat

Reputation: 67752

What you need is a constraint, not a trigger. In 11g:

ALTER TABLE emer_complaint 
   ADD (date_complaint DATE GENERATED ALWAYS AS (trunc(date_time)));

ALTER TABLE emer_complaint 
   ADD CONSTRAINT "one complaint per day" UNIQUE (ip_add, date_complaint);

In 10g and before:

CREATE UNIQUE INDEX "one complaint per day" 
   ON emer_complaint (ip_add, trunc(date_time));

Don't use triggers for (cross-row) integrity.

Upvotes: 3

Related Questions