Re: Problems with triggers

In a previous posting, Frederic Lesieur (Frederic.Lesieur_at_dmr.ca) writes:
> Hy,
>
> My problem is that I want to restrict the access on a table to a user
> under certain condition.
>
> I want this user to be able to update the table in the case where
> a table's field called TYPE is not equal to 'S'. Otherwise, if ='S',
> then Oracle should send a error message saying that the update
> of the table is impossible.
>
> To do that, I have created a BEFORE trigger associated with the
> table that is fired for each update of table.
>
> If the condition is true, i.e. TYPE = 'S', then I issue the following
> error message with the function:
>
> raise_application_error(-20501, 'Update unavailable')
>
> The problem is there. The message appears on the screen, but with
> other messages from Oracle saying that the trigger has
> failed.
>
> ORACLE - 20501 'Update unavailable'
> ORACLE - XXXXX 'ERROR line 1'
> ORACLE - XXXXX 'Error in the execution of trigger'
>
> How can I avoid these last two messages from within a trigger?
>
> Or, is there any other way of stopping an update from within a
> trigger?
>
> The only way that I've found is with trigger.
>
> Thanks!
>
> Martin Miron
>
> e-mail: Martin.Miron_at_dmr.ca

These extra messages show up if the SQL statement being executed that caused that trigger to fire also fails. If it is inside a PL/SQL block, use an exception to capture the trigger failure and you can continue processing.

Dave Boswell Received on Thu Apr 20 1995 - 00:00:00 CEST