The question is proposed: Generally we want to perform some kind of operation based on the record of the database. Our habitual operation is to determine if there is a new record in the procedure. Such a high resource is high, how to improve this efficiency, I want to create a trigger in the table, call the external dynamic connection library in the trigger to notify the application through a message or event to notify the application. It is best to call the external dynamic connection library during the MASTER store, and we call the MASTER stored procedure in the trigger. Download Source Code Size: 14kHttp://www.vckbase.com/vckbase/vckbase 12/vc/nonctrls/database_07/storeproc.zip Description: VC6 needs to install a newest Platform SDK to compile this code, VC.NET can compile directly This code. In addition, it is also necessary to connect OpenDS60.Lib to make a friend who does not have a new Platform SDK can compile this example, which has placed SRV.H and OPENDS60.LIB in VC.NET in the compressed package: let's implement a stored procedure The externally DLL (StoreProc.dll) function setFileName and AddLine are called. Stored procedure is as follows (on the library into the master): CREATE PROCEDURE sp_testdll ASexec sp_addextendedproc 'SetFileName', 'storeproc.dll' - declare function exec sp_addextendedproc 'addLine', 'storeproc.dll' declare @szFileName varchar (200) declare @ szText varchar (200) declare @rt intSelect @szFileName = 'c: /welcome.txt'EXEC @rt = SetFileName @szFileName - SetFileName function call, parameters --szFileName; if @rt = 0beginselect @szText =' welcome 01 'Exec @rt = addLine @szText - call addLineselect @szText =' realized welcome 02'Exec @rt = addLine @szTextendexec sp_dropextendedproc 'SetFileName'exec sp_dropextendedproc' addLine'dbcc SetFileName (free) dbcc addLine (free) dynamic link library : This dynamic connection library is different from ordinary. This dynamic connection library is placed in the execution directory of SQL, or put it directly in the Window's System32 directory, and restart SQL-Server
#include
#include
#define XP_noError 0
#define XP_ERROR 1
#ifndef _debug
#define _debug
#ENDIF
Char SzfileName [MAX_PATH 1];
Void WriteInfo (Const Char * STR);
Extern "C" SRVRETCODE WINAPI SETFILENAME (SRV_PROC * PSRVPROC)
{
WriteInfo ("setfilename start");
INT paramcount = srv_rpcparams (psrvproc);
IF (paramcount! = 1) {
WriteInfo ("Param Err Start");
Return XP_ERROR;
}
BYTE BTYPE;
UNSIGNED Long CBMaxlen;
UNSIGNED Long Cbactuallen;
Bool Fnull;
int RET = SRV_PARAMINFO (Psrvproc, 1, & Btype, & Cbmaxlen, & Cbactuallen,
NULL, & Fnull;
IF (cbactuallen) {
ZeromeMory (SZFileName, Max_Path 1);
Memcpy (SZFileName, SRV_PARAMDATA (Psrvproc, 1), CBACTUALLEN);
WriteInfo ("SET FileName OK");
Return (XP_NoError);
}
Else {
WriteInfo ("SET FileName Param Failed);
Return XP_ERROR;
}
}
Extern "C" SRVRETCODE WINAPI AddLine (SRV_PROC * PSRVPROC)
{
WriteInfo ("Addline Start");
INT paramcount = srv_rpcparams (psrvproc);
IF (paramcount! = 1) {
WriteInfo ("AddLine Param Err";
Return XP_ERROR;
}
BYTE BTYPE;
UNSIGNED Long CBMaxlen;
UNSIGNED Long Cbactuallen;
Bool Fnull;
BOOL RT = FALSE;
int RET = SRV_PARAMINFO (Psrvproc, 1, & Btype, & Cbmaxlen, & Cbactuallen,
NULL, & Fnull;
IF (cbactuallen) {
Int n;
Char SRT [3] = {0x0D, 0x0A, 0};
Char * c = new char [CBACTUALLEN 3];
IF (! c) Return XP_ERROR;
ZeromeMory (C, CBACTUALLEN 3);
Memcpy (C, SRV_PARAMDATA (PSRVPROC, 1), CBACTUALLEN);
Memcpy (C CBACTUALLEN, SRT, 3);
Handle HF = CreateFile (SZFileName, Generic_Write, File_Share_Write | file_share_read, null,
Open_ALWAYS, 0, NULL);
IF (hf == invalid_handle_value) {
WriteInfo ("Addline Create File Err);
Delete [] C;
Return XP_ERROR;
}
WriteInfo ("AddLine Create File OK);
DWORD DWWT;
n = strlen (c);
SetFilePointer (HF, 0, NULL, FILE_END); IF (Writefile (HF, C, N, & DWWT, NULL && DWT == N)
{
WriteInfo ("AddLine Write File OK";
Rt = true;
}
Delete [] C;
CloseHandle (HF);
}
RETURN RT? XP_NOERROR: XP_ERROR;
}
Inline void writeinfo (const char * str) {
#ifdef _Debug
Char SRT [3] = {0x0D, 0x0A, 0};
Handle HF = CREATEFILE ("c: //storeproc.log", generic_write, file_share_write | file_share_read, null,
Open_ALWAYS, 0, NULL);
IF (hf! = invalid_handle_value) {
SetFilePointer (HF, 0, NULL, FILE_END);
DWORD DWWT;
Writefile (HF, STR, STRLEN (STR), & DWWT, NULL
Writefile (HF, SRT, STRLEN (SRT), & DWWT, NULL
CloseHandle (HF);
}
Else {
MessageBox (NULL, "Write Info Err", "Message", MB_ok | MB_ICONInInformation;
}
#ENDIF
}
Bool WinApi Dllmain (Hinstance Hinstdll, DWord FDWREASON, LPVOID LPRESERVED)
{
Return True;
After the completion of the completion, put the dynamic link library into the WinNT / System32 directory, start SQL Server. We can open the SQL Server Query Analyzer call stored procedure sp_testdll to test whether its operation is correct. Refer to SQL-Server online help. Win2000 Professional SQL-Server7.0 (2000 can also) VC6.0 SP5 Platform SDK 20001.8