Fwd: trigger to prevent insert

Forgot to send to c.d.i (informix-list) too. ---------- Forwarded message ---------- From: Jonathan Leffler <jleffler.iiug@gmail.com> Date: Thu, Feb 26, 2009 at 2:58 PM Subject: Re: trigger to prevent insert To: Gentian Hila <genti.tech@gmail.com> On Thu, Feb 26, 2009 at 2:32 PM, Gentian Hila <genti.tech@gmail.com> wrote: > We have a program that was written in C++ and we do not have the code > for it and cannot even trace the programmer so I know that this change > would have been nice to have been done in the program but right now we > cannot do it. So, you need to remove the program from your system and rewrite it (or forget about it) immediately. It is a long-term liability, best dealt with in the short-term. Programs that cannot be recompiled are dangerous to your organization's well-being. > We need to do some validation while we insert records on a table let > say table A. > > If customer_code that the user is trying to insert in table A already > exists in table B then prevent the data from being written. > > The good thing is that the program throws a generic error for unknow > errors and does not crash, but we really do not care about user > feedback in this case. > > Can a trigger be used to do this? If so how? Yes.  Normally, create a stored procedure to validate the data, and have it throw an exception, maybe error -746 and an explanation, when there's a problem.  Then use the CREATE TRIGGER statement (manual bashing) to execute the procedure, passing the new value(s) to the procedure. > Or anything else we could do in the database side to prevent this from > happening? That's one of the reasons triggers are provided - and putting the trigger in the DBMS means that all applications get the benefit of the validation, whereas putting the code client-side means that only some applications (those which you have the source for and recompile - after modifying) benefit from the validation. -- Jonathan Leffler                   #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/ "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." NB: Please do not use this email for correspondence. I don't necessarily read it every week, even. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/ "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." NB: Please do not use this email for correspondence. I don't necessarily read it every week, even.