Re: trigger puzzlement

<Kenneth Koenraadt> wrote in message
news:402be173.1492295_at_news.inet.tele.dk...
> Hi Mike,
>
> I tried to recreate, and I got the error below, which I belive you got
> too. But it has nothing to do with locks.

Ken- thanks for the reply. Initially I was using Q&E to perform the updates that fired the trigger. It would come back and complain about the row being updated by someone else... Then I got smart(er) and use SQL+ to fire the trigger. SQL+ gives me the same 3 errors you received, starting with the 1403.

> ORA-01403: no data found
> ORA-06512: ved "A.AUROW_GRID_CLONE", linje 6
> ORA-04088: fejl under udf°relse af triggeren 'A.AUROW_GRID_CLONE'

> Which means that your intitial check returns no row => no data found
> to put into v_exists => exception is raised. This is the expected
> behaviour.
>
> What you need to do is to catch that exception and handle it
> appropriately.

That's what I'm starting to realise. I was under the mistaken impression that the use of the EXISTS clause would return existance or non-existance of the requested data without raising an error condition.

> You could also make your check more readable (and even
> prettier), like this :
>
> begin
> select 1
> into dummy_var
> from test.t2
> where trunc(last_date) = trunc(sysdate); --today
> and cmdy = :new.cmdy_sym;
>
> -- No exception, There was exactly one row, do the update here
> exception
> when no_data_found then
> -- there was no record for today, do the insert here.
> when too_many_rows then
> -- Something went terribly wrong !
> raise;
> end;

Thanks - I'll try your version!

Mike Received on Thu Feb 12 2004 - 21:45:35 CET