Update triggers

Greetings all, I have a trigger problem: I have a column in a table that when it is modified I need to update another column in a different record but in the same table. I have actually got this to work by using an update trigger on the column in the table and a stored procedure to do the rest of the work, and its works fine, but it only works when I run it "manually" from a inside a dbaccess session or a unix command prompt. When I use our third party application to change the value, which is how it needs to be done, it throws up a -747 error. Now I know what that error means and what is causing it but as it works fine when run manually I do not know why it will not work from inside the 3rd party app. I cannot see the source code for the 3rd party app so do not know what it is doing inside. I have hard-coded values at present and really dumbed down the code so its easy to read as its still in proof of concept stage. There may be some errors in the code below because I have cut and paste and changed some names to protect the innocent, but you should be able to understand it. ---------------------------------------------------------------- CREATE TRIGGER djtest1UPDATE OF col1 ON mytable REFERENCING NEW AS n OLD AS o FOR EACH ROW WHEN(o.id = 123456 AND o.detail_code = "ABC") (EXECUTE PROCEDURE dj_test_calc(o.id, n.col1)) ---------------------------------------------------------------- CREATE PROCEDURE dj_test_calc(l_id INTEGER, l_col1 char(1)); DEFINE newval DECIMAL(13,2); LET newval = 0.00; IF l_col1 = "Y" THEN SELECT a.value - b.value INTO newval FROM mytable a, mytable b WHERE a.id = l_id AND a.detail_code = "DEF" AND b.id = l_id AND b.detail_code = "GHI"; END IF; IF l_col1 = "N" THEN SELECT a.value INTO newval FROM mytable a WHERE a.id = l_id AND a.detail_code = "DEF"; END IF; UPDATE mytable SET value = newval WHERE id = l_id AND detail_code = "XYZ"; END PROCEDURE; ----------------------------------------------------------------- When I issue the following update command UPDATE mytable SET col1 = "Y" WHERE id = 123456 AND detail_code = "ABC" it updates the value in the XYZ record, spot on!!!! When we make the same change inside the application is fails with the -747 error. I am developing on SCO Unix using 9.21.UC2 and my database is 10.00.FC4 and is on SUSE Linux. -------------------------------------------------------------------- Any ideas/guesses why it wont work inside the application and also any other comments you want to make are appreciated. CHEERS

On Mon, Aug 6, 2012 at 9:07 AM, <> wrote: > Greetings all, > > I have a trigger problem: > Dear Anonymous, You have a bigger problem than your trigger problem -- you're anonymous. Please repost non-anonymously. -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --bcaec54fbc7263582404c69d44dd

DAVE JONES is anonymous?. Gee, and I thought I was more anonymous USING "FRANK COMPUTER", better known as "FRANCISCO BOTELO".. Un-characteristic of you, Jonathan Leffler to respond like that, especially since its a good question! Dave: What 3rd-party app, platform and connection method are you using to execute you trigger/SPL on which IDS version/platform? It would seem that you should be able to accomplish your goal by just using a trigger on the server side? Using a trigger/SPL combo doesn't sound like a sound method since its a two-step method and anything could happen in between step 1 and 2. Are other users/procs concurrently accesing this table? How big is the table and how many rows avg. need updating?

Thanks for the replies. >>>DAVE JONES is anonymous?. Sorry but I didn't know I was anonymous. >>>What 3rd-party app, platform and connection method are you using to execute you trigger/SPL on which IDS version/platform? The 3rd party app is called AXXIA Fee Earner Desktop (FED), it is installed locally on each the users PC and is using ODBC to connect to both the SCO and Linux boxes. >>>It would seem that you should be able to accomplish your goal by just using a trigger on the server side? Using a trigger/SPL combo doesn't sound like a sound method since its a two-step method and anything could happen in between step 1 and 2. I did try just using a trigger but could not get it to work correctly and its still in proof of concept stage at present. Once I get the 3rd party app to work correctly I will streamline the code. >>>Are other users/procs concurrently accesing this table? The table is in near constant use but mainly only by other FED users >>>How big is the table and how many rows avg. need updating? The table has 5.5 million rows in it and ONLY one row will be updated at any time, but the update could occur a few hundred times a day at most. Thanks

> Greetings all, > > I have a trigger problem: > > >Dear Anonymous, > >You have a bigger problem than your trigger problem -- you're anonymous. > >Please repost non-anonymously. Now I see what Jonathan means by the OP being anonymous. Although his name appears as DAVE JONES, he has no email address. How was he able to join iiug.org without providing a valid email address?.. It appears as <unknown> when I received the email of his posting.

>>>It would seem that you should be able to accomplish your goal by just using a trigger on the server side? I am struggling to get it to work, is there any chance you could elaborate on how to do it?