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

On 31 Jan, 17:18, brenddie <brend...@gmail.com> 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 if the application does "update where current of " or "delete where current of" as these NEED to be within a being/coimmit pair when using a logged database. As a matter of interest why was the database created a non-logged originally?

On Jan 31, 7:13 pm, "da...@smooth1.co.uk" <da...@smooth1.co.uk> wrote: > On 31 Jan, 17:18, brenddie <brend...@gmail.com> 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 if the application does "update where current of " or "delete > where current of" as these NEED to be within a being/coimmit pair when > using a logged database. > > As a matter of interest why was the database created a non-logged > originally? Im not sure why the DB was created not logged. Maybe it was common practice more than 10 years ago or it gave a performance gain or just made things easier to code. Enabling transactions is in the long term todo list. One of the things holding the conversion to logged is not being able to select across databases when one is logged and the other is not logged. This means both databases need to be converted at the same time to be able to switch on logging.

You can access data from multiple databases when their logging mode is different by establishing separate connections to each database and using the SET CONNECTION statement to switch connections. You cannot join tables in the two databases until both logging modes are the same. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Feb 1, 2010 at 9:37 AM, brenddie <brenddie@gmail.com> wrote: > On Jan 31, 7:13 pm, "da...@smooth1.co.uk" <da...@smooth1.co.uk> wrote: > > On 31 Jan, 17:18, brenddie <brend...@gmail.com> 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 if the application does "update where current of " or "delete > > where current of" as these NEED to be within a being/coimmit pair when > > using a logged database. > > > > As a matter of interest why was the database created a non-logged > > originally? > > Im not sure why the DB was created not logged. Maybe it was common > practice more than 10 years ago or it gave a performance gain or just > made things easier to code. > Enabling transactions is in the long term todo list. One of the things > holding the conversion to logged is not being able to select across > databases when one is logged and the other is not logged. This means > both databases need to be converted at the same time to be able to > switch on logging. > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >

On Feb 1, 2:25 pm, Art Kagel <art.ka...@gmail.com> wrote: > You can access data from multiple databases when their logging mode is > different by establishing separate connections to each database and using > the SET CONNECTION statement to switch connections.  You cannot join tables > in the two databases until both logging modes are the same. > > Art > I'll do some testing with the SET CONNECTION later this week. For the trigger I'm going to try to change the overlapping dates from the SP or maybe insert a row in table so I can keep track of these rows and forward them to whoever should fix them. This should help while we manage to convert to logged. In the next weeks I'll make a new post for the conversion to logged to get some pointers. thanks