Detect new record entered/updated in MS SQL

[Vu Truong]

Hello, My application is required to monitor a database in MS SQL. Can I use C#/ADO.NET to develop the application that is to detect a new record entered or updated in MS SQL? I appreciate your helps.

[Ian Darling]

Vu Truong wrote: My application is required to monitor a database in MS SQL. Can I use C#/ADO.NET to develop the application that is to detect a new record entered or updated in MS SQL? Off the top of my head (I've never tried this, so YMMV), you could add a trigger to the database which on the relevant changes which calls into an extended stored procedure (or, if all you're doing is say, logging changes to a table back into another table for audit purposes, just use the trigger itself to update the log table) An extended stored procedure is an exported function from a DLL (C-style), so you'll probably need to expose your C# code in a manner that SQL Server can understand, or write a C++ wrapper that can interop with your code.

---

Ian Darling "If we've learned anything from history, it's that those who feed trolls are condemned to repetitive conversations. Or something like that." - Eric Lippert

[cmk]

Ian Darling wrote: Off the top of my head (I've never tried this, so YMMV), you could add a trigger to the database which on the relevant changes which calls into an extended stored procedure I recently did just that. I wrote several XP's to send messages to a specified server and port. Of course there is an issue of protocol for the message format. In general to write an XP you need the following : 1. Make sure you use the right srv header : i.e. place C:\PROGRAM FILES\MICROSOFT SQL SERVER\80\TOOLS\DEVTOOLS\INCLUDE before C:\Program Files\Microsoft Visual Studio\VC98\INCLUDE in the list of include directories.

#include <srv.h>    // MSSQL ODS header

2. Make sure you link with the OpenDS library :

#pragma  comment(lib, "opends60")    // MSSQL srv_*() functions

3. Make sure all your XP's are compiled as C not C++. i.e. wrap with

#ifdef __cplusplus
extern "C" {
#endif 
...
#ifdef __cplusplus
}
#endif 

if functions are in a cpp file. 4. Provide a __GetXpVersion() function in your library :

__declspec(dllexport)  ULONG  __GetXpVersion( void )
{
	return(ODS_VERSION);
}

I ended up writing two sets of functions : CMKMSSQL_API SRVRETCODE xp_cmkInitSvcMsg ( SRV_PROC *SP ); CMKMSSQL_API SRVRETCODE xp_cmkTermSvcMsg ( SRV_PROC *SP ); CMKMSSQL_API SRVRETCODE xp_cmkSendSvcMsg ( SRV_PROC *SP ); CMKMSSQL_API SRVRETCODE xp_cmkBroadcastSvcMsg ( SRV_PROC *SP ); and CMKMSSQL_API SRVRETCODE xp_cmkInitGisMsg ( SRV_PROC *SP ); CMKMSSQL_API SRVRETCODE xp_cmkTermGisMsg ( SRV_PROC *SP ); CMKMSSQL_API SRVRETCODE xp_cmkSendGisMsg ( SRV_PROC *SP ); CMKMSSQL_API SRVRETCODE xp_cmkBroadcastGisMsg ( SRV_PROC *SP ); The *SvcMsg ones just send raw data to the specified server:port. The *GisMsg ones send protocol formated messages to one of my GIS servers. xp_cmkInit*Msg is called to create a connection to a specified server:port Usage : EXEC xp_cmkInitSvcMsg <@server> <@port> [@handle OUTPUT] The output handle is a long and is used to by the Term/Send functions. xp_cmkTerm*Msg is called to close a connection. Usage : EXEC xp_cmkTermSvcMsg <@handle> xp_cmkSend*Msg sends a blob of data using the connection associated with the specified handle. Usage : EXEC xp_cmkSendSvcMsg <@handle> <@msg> Because the message is sent using sock->Send( (byte*)srv_paramdata(SP, 2), srv_paramlen(SP, 2) ); The mess