Re: как использовать триггер для отмены вставки/обновления?
Translated from English by DrWatson — View original
Нет, если приложение выполняет «update where current of» или «delete where current of», поскольку эти NEED должны находиться внутри пары begin/commit при использовании журналируемой базы данных. Кстати, интересно, почему база данных изначально была создана без журналирования?
Quoted text and signature (English original)
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 logged). Возможно, более 10 лет назад это было обычной практикой, либо это давало выигрыш в производительности, либо просто упрощало написание кода. Включение транзакций есть в списке долгосрочных задач. Одна из вещей, которая сдерживает перевод в режим с журналированием, — невозможность выполнять выборку из нескольких баз данных, когда одна из них работает с журналированием, а другая — без. Это означает, что для включения журналирования обе базы данных нужно переводить одновременно.
Quoted text and signature (English original)
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?Вы можете обращаться к данным из нескольких баз данных с разными режимами журналирования, устанавливая отдельные соединения с каждой базой данных и используя оператор SET CONNECTION для переключения между соединениями. При этом вы не сможете выполнять соединение (join) таблиц из двух баз данных, пока режимы журналирования обеих баз не станут одинаковыми. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Увидимся на конференции 2010 IIUG Informix Conference 25–28 апреля 2010 г. Оверленд-Парк (Канзас-Сити), штат Канзас www.iiug.org/conf Отказ от ответственности: Прошу учитывать, что мои собственные мнения являются только моими собственными мнениями и не отражают позицию моего работодателя, Advanced DataTools, IIUG, а также любой другой организации, с которой я связан явно, неявно или косвенно. Равным образом эти мнения не отражают мнения других лиц, связанных с какой-либо организацией, с которой связан я, а также мнения самих этих организаций.
Quoted text and signature (English original)
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
>На этой неделе чуть позже проведу тесты с SET CONNECTION. Что касается триггера, попробую изменять пересекающиеся даты из хранимой процедуры либо вставлять строку в таблицу, чтобы отслеживать такие строки и передавать их тем, кто должен их исправлять. Это должно помочь, пока мы не выполним переход на logged. В ближайшие недели создам новую тему о переходе на logged, чтобы получить несколько советов. спасибо
Quoted text and signature (English original)
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
>