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

Hi Ron,

"Ron Reidy" <rereidy_at_indra.com> wrote in message news:3DC28634.7CA018DA_at_indra.com...

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

Well, you're right. My first scratch was with a "wellformed" PL/SQL block, but I went into a wrong direction, because I used NEW.ENT_KWD_ID_Type instead of :NEW.ENT_KWD_ID_Type. Now it works! (see below...). It's always difficult at the beginning!

---
Thanks a lot,
Carsten


CREATE OR REPLACE TRIGGER TGR_CHK_ENT_ID_Type
   BEFORE
   INSERT OR UPDATE OF ENT_KWD_ID_Type
   ON tEntity
   FOR EACH ROW

DECLARE

   CURSOR CRS_Keyword IS
      SELECT UPPER(KWD_Section) KWD_Section
      FROM   tKeyword
      WHERE  KWD_ID = :NEW.ENT_KWD_ID_Type;

   REC_Keyword CRS_Keyword%ROWTYPE;

BEGIN

   OPEN  CRS_Keyword;
   FETCH CRS_Keyword INTO REC_Keyword;

   IF REC_Keyword.KWD_Section != 'ENTITYTYPE'
   THEN
      RAISE_APPLICATION_ERROR
         (-20000,'Invalid KWD_ID for ENT_KWD_ID_Type!');
   END IF;

END;