how to use a trigger to stop an insert/update?
Poster on IDS 11.5 wanted a trigger to block inserts/updates that create overlapping effective/expiration date ranges. A BEFORE trigger can't see the new row values, and raising an exception from the FOR EACH ROW validation procedure didn't prevent the row from being written. Suggestions included a check constraint (rejected, since constraints can't contain subqueries/SPL routines), a unique index (withdrawn as unworkable), and restricting table permissions so users insert only via a DBA stored procedure. Fernando Nunes identified the real cause: in an unlogged database a trigger failure cannot roll back the insert/update, by design. The poster concluded he would convert the database to logging mode.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
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
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?
> > 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? I was getting syntax errors when trying to define constraints using SQL statements. Reading the docs say I cant use SQL statements in a constraint. "Check constraints are defined with search conditions. The search condition cannot contain subqueries, aggregates, host variables, or SPL routines" The validation is a simple select count(*) using the proposed start and end dates. If the count(*) is >= 1 then the update/insert needs to be stopped as It will result in an overlapping as theres already a record in that date range. How can I enforce that no overlappings are created?
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 >
On Jan 30, 6:54 pm, "Bill Hamilton" <garage_...@hotmail.com> wrote: > Why can't you add a unique index on effective_date and expiration_date ? > That should make the bad ones blow out. > On Jan 30, 8:26 pm, "Bill Hamilton" <garage_...@hotmail.com> wrote: > Never mind my last post. > I see your problem ... Your programmers just need to get better on > validation. > Lets say I dont trust the front end and users very much so I want to start building a last line defense in case errors like those slip trough the program validations.
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
On 31 Jan, 11:37, 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 Remove users permissions from the table and have them run a dba stored procedure to do the insert.
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.