ORACLE 7 DB Trigger Error Messages

From: David Clement <clement1_at_llnl.gov>
Date: 21 Jan 1994 21:50:25 GMT
Message-ID: <clement1-210194130557_at_attilla.llnl.gov>

Something a little flaky has come up in error messages from my ORACLE 7 database triggers. I can't find the explanation in the documentation or the FAQ and I hope someone in the newsgroup can advise me.

The environment is SQL*Plus 3.1.2.3.1, PL/SQL 2.0.17.1.0, ORACLE 7.0.15 with procedural and distributed options, under SunOS 4.1.2 on a Sun4 server. The triggers (there are about 25 of them) perform various input and ref-int validations. For example:

CREATE TRIGGER BLAH
BEFORE INSERT ON WOOF
FOR EACH ROW
BEGIN
  IF :NEW.ONE_CODE = 'Y' AND :NEW.OTHER_CD != '99' THEN     RAISE_APPLICATION_ERROR(-20000,
      'Cannot save invalid code combination');   END IF;
END; I can insert with a valid code combination. When I try to insert with an invalid code combination, SQL*Plus responds with --

ORA-20000: Cannot save invalid code combination
ORA-06512: at line 3
ORA-04088: error during execution of trigger 'BLAH'

This behavior is consistent from trigger to trigger, whether the code uses RAISE_APPLICATION_ERROR nakedly (as above) or has a PRAGMA EXCEPTION_INIT to wrap it. The first message is what I wanted; the other two are confusing. There is nothing visibly wrong with the code, and it behaves as expected. So why am I getting the other two messages? Can I turn them off? Should I?

Thanks in advance for your kind attention to my silly question. If your answer is, 'It just does that! Bwah-hah-hah!', I shall understand perfectly.

---

David Clement || Geyr Garmr mjok fyr Gnipahellu. clement1_at_llnl.gov || Festr mun slitna, enn freki renna.

---

Received on Fri Jan 21 1994 - 22:50:25 CET