trigger/stored procedure cancelling action?

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. **********************************************************************

"Todd M. Roy" wrote: > 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. I'd expect to find that RAISE EXCEPTION in a stored procedure should prevent the operation from completing. However, it depends on whether your database has transactions or not. Try: create table t (i integer); create procedure raise_exception(i integer)if i is null then raise exception -746, 0, "insert null"; end if; end procedure; create trigger t_insert insert on treferencing new as new for each row (execute procedure raise_exception(new.i)); insert into t values(0); insert into t values(null); select * from t; select count(*) from t; In an unlogged database, the null is inserted. In a logged database (or a MODE ANSI database), the null is not inserted. -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"

Todd M. Roy wrote in message <91rehr$j4p$1@news.xmission.com>... > > 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. > First, let me congratulate u on your cute signature penguins... If you are using an associated table to implement a "partial index" - ie an index which only references some of the real rows (and I can't think of any other way to do it) then here's how I've done it: main_table: key integer (for example) assoc_table: key integer primary key You no doubt already have this structure or very similar. Now, define insert delete and update triggers on the main_table, which call a little stored procedure to do the work in assoc_table. Define the stored procedure to receive two arguments: a "before" and "after" key. If there is more than one field in the complete key, then of course, pass in before and after values of each. The insert trigger will call proc(NULL, new.key) The delete trigger will call proc(old.key, NULL) The update trigger will call proc(old.key, new.key) The procedure merely needs to decide whether to insert, delete or update the value in the associated table, and thru this design it will also deal with the situations where the keys happen to be null - ie consumes all cases involving insert, delete, update into one simple ritual. Now, the beauty is: since you have a unique constraint (ie unique index - in my example, a primary key) on the associated table, then by ordinary magic, the engine will throw an exception for you if you attempt to violate the uniqueness rule you wish to apply to non-null keys, and therefore there is no special coding you need to apply. The proc will do something like (please forgive my extremely rusty SPL - you correct it and put in the proper semi-colons): -- bail out on the "nothing to do" cases if old.key is null and new.key is null then return end if if old.key = new.key then return end if -- now, at most, one field is null if old.key is null then -- no old key to deal with -- only need to insert new key insert new.key into assoc_table ... elif new.key is null then -- no new key to deal with -- only need to delete old key delete old.key from assoc_table ... else -- neither key is null, so update update assoc_table ... end if And the job is done. Simple programming, with comments too! I suggest you name the stored procedure after the main table, with a wart - ie some little code like pi_main_table where pi_ is the wart meaning "partial index" and lets all programmers know because you have a 100% applied naming convention... Please let me know if this does what you want to do. Good luck on your mission.