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

"Carsten Alexander" <acamat_at_web.de> schreef in bericht news:aptvhs$50fpg$1_at_ID-86275.news.dfncis.de...
> 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. ;)

I'm sorry if this might sound rude, but I think your problem is that you do not distinguish between SQL and PL/SQL. You use typical SQL expressions in an PL/SQL context.
Try the following.

CREATE OR REPLACE TRIGGER TGR_CHK_ENT_KWD_ID_Type BEFORE
INSERT OR UPDATE OF ENT_KWD_ID_Type ON tEntity FOR EACH ROW
   ID_TEST number;
BEGIN
    BEGIN

       SELECT 1 INTO ID_TEST
           FROM tKeyword
           WHERE UPPER(KWD_Section)='ENTITYTYPE'
              AND :NEW.ENT_KWD_ID_Type  = KWD_ID
    EXCEPTION
       WHEN NO_DATA_FOUND THEN
          RAISE_APPLICATION_ERROR
           (-20000,'Invalid KWD_ID for ENT_KWD_ID_Type!');
     END;