Re: Trigger Question - Error

Thomas J. Kyte wrote:
>
> 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

Thanks for the reply but the whole idea is to catch the error I wrote the trigger to catch..... The second error is the one I'm trying to get away from. I have an idea from another person, that is:

1. Because you are using an implicit cursor (select .. into ...) you *must* code an exception for NO_DATA_FOUND. 1a) Explicit cursors are almost always to be preferred for this and a number of other reasons.
2. Trigger code is better written with *no* SQL at all. It is preferable to make calls (references) to stored procedures / functions, which, again preferably, should be packaged.

<Name deleted in case the person responding via email does not want their name posted.. But many thanks to you know who. >

I'll try this tomorrow, Thanks JB Received on Thu Mar 06 1997 - 00:00:00 CST