Re: trigger/stored procedure cancelling action?

Throwing an exception from the trigger if the value is already in the table will work? HTH, Radu "Todd M. Roy" wrote: > All, > > Is there anyway for a trigger or stored procedure to cancel > (ie cause to not execute) the actual triggering action? > > The problem is that some application programmers want to simulate > an index where if the value is non-null, it must be unique, otherwise > any number of nulls are allowed. > > Thanks, > Todd. > > And yes I am Reading The Fine Manuals. > -- > .~. Todd Roy, Senior Database Administrator .~. > /V\\ Holstein Association, U.S.A. Inc. /V\\ > // \\\\ troy@holstein.com // \\\\ > /( )\\ 1-802-254-4551x4230 /( )\\ > ^^-^^ ^^-^^ > ********************************************************************** > This footnote confirms that this email message has been swept by > MIMEsweeper for the presence of computer viruses. > **********************************************************************

In article <91s1sh$p5u$1@news.xmission.com>, Radu Dumitriu <radu@goldenclick.com> wrote: > > Throwing an exception from the trigger if the value is already in the > table > will work? > > HTH, > Radu > > "Todd M. Roy" wrote: > > > All, > > > > Is there anyway for a trigger or stored procedure to cancel > > (ie cause to not execute) the actual triggering action? > > > > The problem is that some application programmers want to simulate > > an index where if the value is non-null, it must be unique, otherwise > > any number of nulls are allowed. > > > > Thanks, > > Todd. > > > > And yes I am Reading The Fine Manuals. > > -- > > .~. Todd Roy, Senior Database Administrator .~. > > /V\\ Holstein Association, U.S.A. Inc. /V\\ > > // \\\\ troy@holstein.com // \\\\ > > /( )\\ 1-802-254-4551x4230 /( )\\ > > ^^-^^ ^^-^^ > > ********************************************************************** > > This footnote confirms that this email message has been swept by > > MIMEsweeper for the presence of computer viruses. > > ********************************************************************** > > Hi, hope I can give you some help.... You can create a trigger on insert and update events of your table, calling the following procedure: create procedure sp_uniqueness(p_value integer) define v_value integer; define v_quantity smallint; if p_value is not null then select value,count(*) into v_value,v_quantity from mytable where value = p_value group by 1; if v_quantity > 1 then raise exception -746,0,"Value not unique"; end if end if end procedure; It worked for me. I use this schema in many tables as business rules... -- Paulo Roberto Marelli de Amorim TS&0 Consulting Brasil -- Paulo Roberto Marelli de Amorim TS&0 Consulting Brasil Sent via Deja.com http://www.deja.com/