IDS Forum

Re: Pre-insert trigger that stops the insert.

Posted By: Art Kagel
Date: Tuesday, 6 January 2009, at 11:15 a.m.

In Response To: Re: Pre-insert trigger that stops the insert. (rroussey@comcast.net)

Unless he defers constraint checking to commit time, then the delete and
insert will have occurred before the check.

Art

On Mon, Jan 5, 2009 at 10:39 PM, rroussey@comcast.net
<rroussey@comcast.net>wrote:

> If your 'option 1' is within the scope of a single tx, it will error as
> usual.
> The delete will not have been completed when the insert occurs.
>
> --
> Bob
>
> -------------- Original message --------------
> From: "Jarrod Teale" <Jarrod.Teale@fonterra.com>
>
> > Hi,
> > Does anyone know how to create a trigger and stored procedure that stops
> > an insert from occuring if it is a duplicate, while returning success?
> >
> > We have a client that can't cope with getting a -239 error when there is
> > a duplicate record on the unique index. This client needs to treat the
> > -239 as "everything is okay" but other errors should still raise the
> > error.
> > So, what I tried was to create a trigger and procedure that looked at
> > the row coming in, checked the target table to see if the primary key
> > exists and if it does, stop the insert from proceeding and just return
> > "all ok".
> > Working out if there is a duplicate is easy - but then stopping the
> > insert from proceeding or overriding the -239 code that comes back seems
> > much more difficult (hence the question).
> >
> > Options I have considered are:
> > 1. Use the pre trigger to delete the record that will cause the clash
> > and let the insert proceed
> > 2. Use a dummy table that the insert goes into, then a trigger on that
> > table moves the record over to the real table if there is no duplicate.
> >
> > Both will work, but the option to override the returned error code /
> > prevent the insert from running would be great - anyone know how?
> >
> > Informix IDS11.10.UC2W2
> >
> > Thanks
> >
> > Jarrod Teale
> >
> > Team Lead - Manufacturing Execution Systems
> >
> > Automation & Process Control Group
> >
> > NZ Technical
> >
> > Fonterra
> >
> > Fonterra Extn: 77525
> >
> > DDI: +64 7 850 7525
> >
> > Mobile: +64 21 968 364
> > fax: +64 7 849 7855
> > email: jarrod.teale@fonterra.com
> >
> >
> >
> >
>
> *******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)

Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.

Messages In This Thread

• Pre-insert trigger that stops the insert.
  Jarrod Teale -- Monday, 5 January 2009, at 10:08 p.m.
  • Re: Pre-insert trigger that stops the insert.
    rroussey@comcast.net -- Monday, 5 January 2009, at 10:39 p.m.
    • Re: Pre-insert trigger that stops the insert.
      Art Kagel -- Tuesday, 6 January 2009, at 11:15 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.