Re: Trigger Question - Error

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 1997/03/06
Message-ID: <33202e66.6755654@nntp.mediasoft.net>#1/1

On Tue, 04 Mar 1997 18:06:59 -0500, John Bringolf <jbringolf_at_uncfsu.campus.mci.net> wrote:

>I keep getting an error on the following trigger - any suggestions:
>
>--- TRIGGER CODE BELOW ----
>
>create or replace trigger check_attach
>before update of attachment on TABLE1 FOR EACH ROW
>declare
> Invalid_Entry EXCEPTION;
> test varchar2(4);
>begin
> select SOMETHING into test
> from Z.PERS WHERE SSAN = :old.ssan;
> if ( :new.attachment IS NOT NULL
> AND :new.attachment = test ) THEN
> RAISE Invalid_Entry;
> END IF;
> EXCEPTION
> WHEN Invalid_Entry THEN
> RAISE_APPLICATION_ERROR (-20001,'Test');
>end;
>
>----- When Testing I get: -----
>
>ERROR at line 1:
>ORA-20001: Test
>ORA-06512: at line 13
>ORA-04088: error during execution of trigger 'MED.CHECK_MED_ATTACH'
>
>Thanks JB

Apparently

• the value of SOMETHING for the :old.ssan select is equal to the :new.attachment value

and

• :new.attachment is not null

Which is causing you the raise the invalid entry exception, which you catch and turn into an application error. The first error in the stack is from you, return code -20001, error message 'Test'.

Change the value of :new.attachement and you won't trigger your own error handling....

Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com                          

http://govt.us.oracle.com

• Check out Oracle Governments web site! ----- Follow the link to "Tech Center" and then downloadable Utilities for some free software...

  ---

statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Thu Mar 06 1997 - 00:00:00 CST