Fw: how to use a trigger to stop an insert/update?

Never mind my last post. I see your problem ... Your programmers just need to get better on validation. > -------------------------------------------------- > From: "Bill Hamilton" <garage_dba@hotmail.com> > Sent: Saturday, January 30, 2010 4:54 PM > To: "John" <jgleipold@gmail.com>; <informix-list@iiug.org> > Subject: Re: how to use a trigger to stop an insert/update? > >> Why can't you add a unique index on effective_date and expiration_date ? >> That should make the bad ones blow out. >> >> -------------------------------------------------- >> From: "John" <jgleipold@gmail.com> >> Sent: Saturday, January 30, 2010 12:16 PM >> Newsgroups: comp.databases.informix >> To: <informix-list@iiug.org> >> Subject: Re: how to use a trigger to stop an insert/update? >> >>> On Jan 29, 10:36 pm, brenddie <brend...@gmail.com> wrote: >>>> I have some tables that have effective and expiration dates. Once in a >>>> while someone manages to create records with overlapping dates causing >>>> "duplicates". Im trying to define a trigger with a validation that >>>> will stop any insert/update that would result in an overlapping. >>>> A BEFORE trigger seems to be what I need but I cant reference the >>>> inserted/updated row when using BEFORE. Using FOR EACH ROW gives me >>>> access to the row so I can run the validation using values from the >>>> row but the row gets inserted/updated before the validation runs. Im >>>> trowing an exception from the stored procedure being used for >>>> validation but that does not stop the row from being inserted. >>>> How can I stop the insert/update ? >>>> This is a not logged database on IDS 11.5 >>> >>> Can you build a check constraint that can do the check? >>> _______________________________________________ >>> Informix-list mailing list >>> Informix-list@iiug.org >>> http://www.iiug.org/mailman/listinfo/informix-list >>> >> _______________________________________________ >> Informix-list mailing list >> Informix-list@iiug.org >> http://www.iiug.org/mailman/listinfo/informix-list >>