Re: PL/SQL: Subqueries in triggers...

Carsten Alexander wrote:
>
> Hi there,
>
> who can I use subqueries in triggers? First I tried...
>
> > CREATE OR REPLACE TRIGGER TGR_CHK_ENT_KWD_ID_Type
> > BEFORE
> > INSERT OR UPDATE OF ENT_KWD_ID_Type ON tEntity
> > FOR EACH ROW
> > BEGIN
> > IF :NEW.ENT_KWD_ID_Type NOT IN
> > (SELECT KWD_ID FROM tKeyword WHERE UPPER(KWD_Section)='ENTITYTYPE')
> > THEN
> > RAISE_APPLICATION_ERROR
> > (-20000,'Invalid KWD_ID for ENT_KWD_ID_Type!');
> > END IF;
> > END;
>
> This does not work (PLS-00405: subquery not allowed in this context!). I tried
> something else by declaring a cursor...
>
> > CREATE OR REPLACE TRIGGER TGR_CHK_ENT_KWD_ID_Type
> > BEFORE
> > INSERT OR UPDATE OF ENT_KWD_ID_Type ON tEntity
> > FOR EACH ROW
> > BEGIN
> > CURSOR CRS_KWD_Section IS
> > SELECT UPPER(KWD_Section)
> > FROM tKeyword
> > WHERE KWD_ID = :NEW.ENT_KWD_ID_Type;
> > IF CRS_KWD_Section != 'ENTITYTYPE' THEN
> > RAISE_APPLICATION_ERROR
> > (-20000,'Invalid KWD_ID for ENT_KWD_ID_Type!');
> > END IF;
> > END;
>
> This does not work either (PLS-00103: Encountered the symbol
> "CRS_KWD_SECTION"...). I have been working serveral hours now to check
> alternatives but according to my limited experience with PL/SQL I can't fix it.
>
> Can someone help me out?
>
> --
> Thanks,
> Carsten
>
> P.S.: I've been using a check constraint on ENT_KWD_ID_Type so far, but
> according to enhancements in the database model (it's still in development) I
> always forget to recompile the constraint, of cause. This "dynamic" approach
> would be much more comfortable. ;)

Cursors must be declared in a declare section of a PL/SQL block. Your example clearly is not.

-- 
Ron Reidy
Oracle DBA