Re: trigger to prevent insert

Thank you everybody. Jonathan, you're absolutely right. In the long term we will move to a new program - Java based open source. But this will be a temporary solution which is not the best solution - I understand. But I think it will help in the meantime. On Thu, Feb 26, 2009 at 5:59 PM, Jonathan Leffler <jleffler.iiug@gmail.com> wrote: > 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. > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >