f



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

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
0
brenddie (5)
1/30/2010 5:36:20 AM
comp.databases.informix 16083 articles. 0 followers. Post Follow

12 Replies
4222 Views

Similar Articles

[PageSpeed] 37

On Jan 29, 10:36=A0pm, 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?
0
John
1/30/2010 6:16:28 PM
> > 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?
0
brenddie
1/30/2010 8:19:49 PM
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
> 
0
Bill
1/30/2010 10:54:21 PM
On Jan 30, 6:54=A0pm, "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=A0pm, "Bill Hamilton" <garage_...@hotmail.com> wrote:
> =A0Never mind my last post.
> =A0I 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.
0
brenddie
1/31/2010 12:39:16 AM
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
0
Fernando
1/31/2010 11:37:12 AM
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
> =A0 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.
0
david
1/31/2010 1:49:30 PM
On Jan 31, 7:37=A0am, 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
> =A0 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.

0
brenddie
1/31/2010 5:18:16 PM
On 31 Jan, 17:18, brenddie <brend...@gmail.com> wrote:
> On Jan 31, 7:37=A0am, 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 causin=
g
> > > "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 th=
e
> > =A0 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?
0
david
1/31/2010 11:13:21 PM
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.
0
Fernando
2/1/2010 12:23:13 AM
On Jan 31, 7:13=A0pm, "da...@smooth1.co.uk" <da...@smooth1.co.uk> wrote:
> On 31 Jan, 17:18, brenddie <brend...@gmail.com> wrote:
>
>
>
>
>
> > On Jan 31, 7:37=A0am, Fernando Nunes <domusonl...@gmail.com> wrote:
>
> > > brenddie wrote:
> > > > I have some tables that have effective and expiration dates. Once i=
n a
> > > > while someone manages to create records with overlapping dates caus=
ing
> > > > "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. I=
m
> > > > 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 wil=
l
> > > not rollback the INSERT/UPDATE. That's by design.
> > > But if you're using non-logged database these duplications should be =
the
> > > =A0 least of your worries... Every failing instruction will leave you=
r
> > > 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.
0
brenddie
2/1/2010 2:37:17 PM
--001517475ec2d587cc047e8e1dc1
Content-Type: text/plain; charset=ISO-8859-1

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
>

--001517475ec2d587cc047e8e1dc1
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

You can access data from multiple databases when their logging mode is diff=
erent by establishing separate connections to each database and using the S=
ET CONNECTION statement to switch connections.=A0 You cannot join tables in=
 the two databases until both logging modes are the same.<br>

<br>Art<br><br clear=3D"all">Art S. Kagel<br>Advanced DataTools (<a href=3D=
"http://www.advancedatatools.com">www.advancedatatools.com</a>)<br>IIUG Boa=
rd of Directors (<a href=3D"mailto:art@iiug.org">art@iiug.org</a>)<br><br>S=
ee you at the 2010 IIUG Informix Conference<br>

April 25-28, 2010<br>Overland Park (Kansas City), KS<br><a href=3D"http://w=
ww.iiug.org/conf">www.iiug.org/conf</a><br><br>Disclaimer: Please keep in m=
ind that my own opinions are my own opinions and do not reflect on my emplo=
yer, Advanced DataTools, the IIUG, nor any other organization with which I =
am associated either explicitly, implicitly, or by inference. =A0Neither do=
 those opinions reflect those of other individuals affiliated with any enti=
ty with which I am affiliated nor those of the entities themselves.<br>

<br>
<br><br><div class=3D"gmail_quote">On Mon, Feb 1, 2010 at 9:37 AM, brenddie=
 <span dir=3D"ltr">&lt;<a href=3D"mailto:brenddie@gmail.com">brenddie@gmail=
..com</a>&gt;</span> wrote:<br><blockquote class=3D"gmail_quote" style=3D"ma=
rgin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding=
-left: 1ex;">

On Jan 31, 7:13=A0pm, &quot;<a href=3D"mailto:da...@smooth1.co.uk">da...@sm=
ooth1.co.uk</a>&quot; &lt;<a href=3D"mailto:da...@smooth1.co.uk">da...@smoo=
th1.co.uk</a>&gt; wrote:<br>
&gt; On 31 Jan, 17:18, brenddie &lt;<a href=3D"mailto:brend...@gmail.com">b=
rend...@gmail.com</a>&gt; wrote:<br>
&gt;<br>
&gt;<br>
&gt;<br>
&gt;<br>
&gt;<br>
&gt; &gt; On Jan 31, 7:37=A0am, Fernando Nunes &lt;<a href=3D"mailto:domuso=
nl...@gmail.com">domusonl...@gmail.com</a>&gt; wrote:<br>
&gt;<br>
&gt; &gt; &gt; brenddie wrote:<br>
&gt; &gt; &gt; &gt; I have some tables that have effective and expiration d=
ates. Once in a<br>
&gt; &gt; &gt; &gt; while someone manages to create records with overlappin=
g dates causing<br>
&gt; &gt; &gt; &gt; &quot;duplicates&quot;. Im trying to define a trigger w=
ith a validation that<br>
&gt; &gt; &gt; &gt; will stop any insert/update that would result in an ove=
rlapping.<br>
&gt; &gt; &gt; &gt; A BEFORE trigger seems to be what I need but I cant ref=
erence the<br>
&gt; &gt; &gt; &gt; inserted/updated row when using BEFORE. Using FOR EACH =
ROW gives me<br>
&gt; &gt; &gt; &gt; access to the row so I can run the validation using val=
ues from the<br>
&gt; &gt; &gt; &gt; row but the row gets inserted/updated before the valida=
tion runs. Im<br>
&gt; &gt; &gt; &gt; trowing an exception from the stored procedure being us=
ed for<br>
&gt; &gt; &gt; &gt; validation but that does not stop the row from being in=
serted.<br>
&gt; &gt; &gt; &gt; How can I stop the insert/update ?<br>
&gt; &gt; &gt; &gt; This is a not logged database on IDS 11.5<br>
&gt;<br>
&gt; &gt; &gt; If you&#39;re using non-logged database than a failure on th=
e trigger will<br>
&gt; &gt; &gt; not rollback the INSERT/UPDATE. That&#39;s by design.<br>
&gt; &gt; &gt; But if you&#39;re using non-logged database these duplicatio=
ns should be the<br>
&gt; &gt; &gt; =A0 least of your worries... Every failing instruction will =
leave your<br>
&gt; &gt; &gt; database in an inconsistent state...<br>
&gt;<br>
&gt; &gt; &gt; Regards<br>
&gt;<br>
&gt; &gt; I see. This is an old system that for some reason the DB is not<b=
r>
&gt; &gt; logged. I&#39;ve been doing some reading and it should be pretty =
straight<br>
&gt; &gt; forward to go from not-logged to logged.<br>
&gt;<br>
&gt; Not if the application does &quot;update where current of &quot; or &q=
uot;delete<br>
&gt; where current of&quot; as these NEED to be within a being/coimmit pair=
 when<br>
&gt; using a logged database.<br>
&gt;<br>
&gt; As a matter of interest why was the database created a non-logged<br>
&gt; originally?<br>
<br>
Im not sure why the DB was created not logged. Maybe it was common<br>
practice more than 10 years ago or it gave a performance gain or just<br>
made things easier to code.<br>
Enabling transactions is in the long term todo list. One of the things<br>
holding the conversion to logged is not being able to select across<br>
databases when one is logged and the other is not logged. This means<br>
both databases need to be converted at the same time to be able to<br>
switch on logging.<br>
_______________________________________________<br>
Informix-list mailing list<br>
<a href=3D"mailto:Informix-list@iiug.org">Informix-list@iiug.org</a><br>
<a href=3D"http://www.iiug.org/mailman/listinfo/informix-list" target=3D"_b=
lank">http://www.iiug.org/mailman/listinfo/informix-list</a><br>
</blockquote></div><br>

--001517475ec2d587cc047e8e1dc1--
0
Art
2/1/2010 6:25:37 PM
On Feb 1, 2:25=A0pm, 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. =A0You cannot join ta=
bles
> 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
0
brenddie
2/1/2010 8:50:21 PM
Reply: