Re: Detecting trigger or stored procedure execution from client side

You could create a reference table and have the trigger/SP insert into it when it executes and then have the client app refer to this table. Klunky, but it should work. SC > I am working with a customer to define and develop a means for detetcing the > firing of tirggers (optimally the method would be able to detect the > execution of stored procedures as well). > > Does any one have any ideas? > > All assistance gratefully accepted. > > Stephen Fernandez > Categoric Software

"Stephen F. Cawley" wrote: > You could create a reference table and have the trigger/SP insert into it when > it executes and then have the client app refer to this table. Klunky, but it > should work. > > SC > > > I am working with a customer to define and develop a means for detetcing the > > firing of tirggers (optimally the method would be able to detect the > > execution of stored procedures as well). > > > > Does any one have any ideas? > > > > All assistance gratefully accepted. > > > > Stephen Fernandez > > Categoric Software We've actually implemented this klunky method by adding a line into all Stored procedures that we wanted to monitor, as follows EXECUTE PROCEDURE update_call_stats('<procedure/trigger name>'); The overheads are obvious, as is the target table where info is collated. In our implementation, the perceived benefits exceeded the costs. I would really like to find a better method (something in sysmaster ideally), but have failed so far. If anyone has bright ideas, let them fly. The procedure code : CREATE PROCEDURE update_call_stats( l_proc_name like call_stats.proc_name ){ This procedure can be used to record date-wise calls of a procedure/trigger. } UPDATE call_stats SET no_of_calls = no_of_calls + 1 WHERE proc_name = l_proc_name AND call_date = TODAY; IF DBINFO('sqlca.sqlerrd2') = 0 THEN INSERT INTO call_stats ( proc_name, call_date, no_of_calls) VALUES (l_proc_name, TODAY, 1);END IF; END PROCEDURE; Rudy ----------------------------------------------------------------------- The nice thing about Windows is - It does not just crash, it displays a dialog box and lets you press 'OK' first. ----------------------------------------------------------------------- Linux...... The choice of a gnu generation. http://www.linux.org ----------------------------------------------------------------------- Informix .. You can do it. -----------------------------------------------------------------------

Informix audit system has an event 'EXSP Execute SPL Routine' that you can use for getting statistics by SP. Triggers is worse because you have to work with triggers events insert/update/delete for table. Anyway you can use audit system for triggers as well as for SP. Eugene In article <39AC05F5.368CA28F@americasm01.nt.com>, Rudy Fernandes <rferdy@americasm01.nt.com> wrote: > "Stephen F. Cawley" wrote: > > > You could create a reference table and have the trigger/SP insert into it when > > it executes and then have the client app refer to this table. Klunky, but it > > should work. > > > > SC > > > > > I am working with a customer to define and develop a means for detetcing the > > > firing of tirggers (optimally the method would be able to detect the > > > execution of stored procedures as well). > > > > > > Does any one have any ideas? > > > > > > All assistance gratefully accepted. > > > > > > Stephen Fernandez > > > Categoric Software > > We've actually implemented this klunky method by adding a line into all Stored > procedures that we wanted to monitor, as follows > > EXECUTE PROCEDURE update_call_stats('<procedure/trigger name>');> > The overheads are obvious, as is the target table where info is collated. In our > implementation, the perceived benefits exceeded the costs. > > I would really like to find a better method (something in sysmaster ideally), but > have failed so far. If anyone has bright ideas, let them fly. > > The procedure code : > > CREATE PROCEDURE update_call_stats( > l_proc_name like call_stats.proc_name > )> { > This procedure can be used to record date-wise calls of a procedure/trigger. > } > > UPDATE call_stats > SET no_of_calls = no_of_calls + 1 > WHERE proc_name = l_proc_name > AND call_date = TODAY; > > IF DBINFO('sqlca.sqlerrd2') = 0 THEN > INSERT INTO call_stats ( proc_name, call_date, no_of_calls) > VALUES (l_proc_name, TODAY, 1);> END IF; > > END PROCEDURE; > > Rudy > ---------------------------------------------------------------------- - > The nice thing about Windows is - It does not just crash, it displays a > dialog box and lets you press 'OK' first. > ---------------------------------------------------------------------- - > Linux...... The choice of a gnu generation. http://www.linux.org > ---------------------------------------------------------------------- - > Informix .. You can do it. > ---------------------------------------------------------------------- - > > Sent via Deja.com http://www.deja.com/ Before you buy.