i am using a trigger with select statement that contains a subquerie. this gives me a recursive error
SQL Error: ORA-00036: maximum number of recursive SQL levels (50) exceeded
i was just wondering what's the standard way to call a such querie as below to use in a trigger. thanks.
create or replace trigger chkBlkLst
before insert on Email
referencing new as nrow
for each row
declare
num integer;
begin
select count(*) into num from EmailList where EmailList.mailto = (select mailto from Contact where contactID = :nrow.emailFrom);
if num>0 then
raise_application_error(-20001, 'spam email detected');
else
insert into Email values(:nrow.emailID, :nrow.emailFrom, :nrow.emailDate, :nrow.emailSubject, :nrow.emailBody, :nrow.emailFlag);
end if;
end;
/