Cannot create Trigger, won't show me errors

From: Marian Aldenhövel <marian_at_mba-software.de>
Date: Sun, 16 Oct 2005 07:51:36 +0200
Message-ID: <disppf$ed7$1@newsreader3.netcologne.de>

Hi,

I am currently porting a database from Firebird to Oracle. We have triggers that fill in PK-values on INSERTs whenever the client does not supply them.

I have been trying for the last two hours to convert that scheme to Oracle 9.2 without success. What frustrates me about it is that nobody inside this machine wants to tell me what I am doing wrong. It just refuses to work.

What I am trying is something like:

   CREATE OR REPLACE TRIGGER CMS.TRG_ARCHIVE_BI0    BEFORE INSERT ON CMS.ARCHIVE
   FOR EACH ROW
   BEGIN

     IF ( NVL(:NEW.RECID,0)=0 ) THEN
       SELECT CMS.GEN_GENERAL.NEXTVAL INTO :NEW.RECID FROM DUAL
     END IF;

   END; The table is there. The Sequence is there. The SELECT (w/o INTO) works.

If I put this code into FreeTOADs procedure editor all it tells me is that it's Invalid. So says Oracle if I try an INSERT into CMS.ARCHIVE. If I put it into SQL*Plus Worksheet it agrees with the TOAD:

   Warnung: Trigger wurde mit Kompilierungsfehlern erstellt.

"Trigger created with compilation errors" or something along that line.

If I do a SHOW ERRORS; after that I get

   Keine Fehler

"No errors". I feel like it is making fun of me...

Where is the joke I am not getting? What is broken with my trigger, how can I fix it, and how do I make this thing tell me what it does not like?

Ciao, MM

-- 
Marian Aldenhövel, Rosenhain 23, 53123 Bonn. +49 228 624013.
http://www.marian-aldenhoevel.de


"And starting today all passwords must contain letters, numbers,

  doodles, sign language and squirrel noises." http://tinyurl.com/csu7p

Received on Sun Oct 16 2005 - 00:51:36 CDT