How to monitor SQL server database table from delphi application

Question

How can I monitor specific table from Delphi application (real time) and get new and updated records data ?

Answer 1

In general, you can use database events / notifications / alerts. The exact term and implementation depends on a DBMS. For details you can check "DBMS alert mechanisms" at AnyDAC documentation.

On a backend you may need to implement triggers for required tables. For some DBMS it is not required. For example, with Firebird a trigger has to call POST_EVENT statement. With SQL Server the special set of Query Update Notification API operators must be called to prepare a table.

On a client you should use either special API, either standard SQL query mechanism. For example, with Firebird the special event API must be used. With Oracle a background thread + standard SQL API.

The Delphi implementation depends on a DBMS and data access components. Some examples:

• dbGo (ADO) does not support notifications, when they are implemented using special API;
• dbExpress - the same;
• IBX - use TIBEvents with Firebird;
• AnyDAC - use TADEventAlerter. It supports many different mechanisms for many DBMS in a unified way. Disclosure: AnyDAC is the flagship product of the company I represent.