Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Using subqueries in Triggers

512330May 12 2006 — edited May 13 2006
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;
/
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 10 2006
Added on May 12 2006
4 comments
684 views