Pre-insert trigger that stops the insert.

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 <mailto:jarrod.teale@fonterra.com>

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. >

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.