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

brenddie wrote: > On Jan 31, 7:37 am, Fernando Nunes <domusonl...@gmail.com> wrote: >> brenddie 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 >> If you're using non-logged database than a failure on the trigger will >> not rollback the INSERT/UPDATE. That's by design. >> But if you're using non-logged database these duplications should be the >> least of your worries... Every failing instruction will leave your >> database in an inconsistent state... >> >> Regards > > I see. This is an old system that for some reason the DB is not > logged. I've been doing some reading and it should be pretty straight > forward to go from not-logged to logged. > Not so simple... I mean... From the DBA point of view is pretty easy. But from the application/developer, there are a lot of things that change. I don't have a list at hand, but there are several instructions that behave differently in logged vs non-logged databases. Some instructions must be inside a BEGIN WORK (like LOCK TABLE), others aren't accepted (UNLOCK TABLE) in logged databases. Some digging in the manual is required, but above all, full application testing... Regards.