Re: Delete trigger

From: Mark D Powell <mark.powellNOmaSPAM_at_eds.com.invalid>
Date: 2000/07/13
Message-ID: <0a91d3e4.5845fb07@usw-ex0102-015.remarq.com>#1/1

"Yong" <yhuang_at_slb.com> wrote:
>Maybe I misunderstand. But could you simply
>
>(1) put null;
>
>(2) or put raise_application_error(-20002,'');
>
>in the trigger body?
>
>Yong Huang
>
>Mark D Powell <mark.powellNOmaSPAM_at_eds.com.invalid> wrote in
 message
>news:0236ff0d.199f06d4_at_usw-ex0102-015.remarq.com...
>> "Yann Cauchard" <yann.cauchard_at_spcconsultants.com> wrote:
>> >Hello,
>> >
>> >I have to avoid deleting lines in a table using a trigger.
>> >I started to write a before delete trigger, which returns
 some
 error
>> >messages.
>> >But i'd like to return no messages, or a custom message.
>> >Can someone help ?
>> >
>> >Thanks in advance.
>> >
>> >Yann CAUCHARD
>> >
>> The only way I can think of to stop the delete would be to
>> raise_application_error(-20002,'Deletes are not allowed').
 This
>> will allow you to send a message, but I do not know any way to
>> send no message, but depending on the front-end applicaion you
>> should be able to control what the user sees.
>>

If you want to allow the delete in some cases and disallow it in others you can code logic, to do nothing and allow the delete in one case, and to raise an error in others. The only way I know to stop the delete from taking place is to call raise_application_error to stop execution and cause a rollback since you can not issue an explicit commit or rollback from a database table trigger.

The raise_application_error routine requires at least two parameters. The code below issues a syntax error when only the error code is passed but you can pass a NULL for the message. But if your user is not in SQL*Plus it will be up to your application to determine what if any message the user sees as your application should be checking the sql return code to see if the statement was successful or failed.

OPS2> @testp
OPS2> set echo on
OPS2> set serveroutput on
OPS2> begin

  2 raise_application_error(-20002,NULL);   3 end ;
  4 /
begin
*
ERROR at line 1:
ORA-20002: << No msg text but msg area exists ORA-06512: at line 2

---

Got questions? Get answers over the phone at Keen.com. Up to 100 minutes free!
http://www.keen.com Received on Thu Jul 13 2000 - 00:00:00 CDT