問題的提出：
一般我們要根據數據庫的紀錄變化時，進行某種操作。我們習慣的操作方式是在程序中不停的查詢表，判斷是否有新紀錄。這樣耗費的資源就很高，如何提高這種效率，我想在表中創建觸發器，在觸發器中調用外部動態連接庫通過消息或事件通知應用程序就可實現。而master的存儲過程中最好能調用外部的動態連接庫，我們在觸發器中調用master的存儲過程即可。
說明:VC6需要安裝較新的Platform SDK才能順利編譯本代碼，VC.Net可以直接編譯本代碼。另外還需要連接Opends60.lib
為了使沒有較新Platform SDK的朋友也能編譯本例子，已經將VC.Net中的Srv.h和Opends60.lib放到壓縮包中
程序實現：
我們來實現一個存儲過程中調用外部的dll(storeproc.dll)的函數SetFileName和addLine。
存儲過程如下(需放到master庫中)：
CREATE PROCEDURE sp_testdll AS
exec sp_addextendedproc 'SetFileName', 'storeproc.dll' --聲明函數
exec sp_addextendedproc 'addLine', 'storeproc.dll'
declare @szFileName varchar(200)
declare @szText varchar(200)
declare @rt int
Select @szFileName = 'c:\welcome.txt'
EXEC @rt = SetFileName @szFileName --調用SetFileName函數，參數為--szFileName;
if @rt = 0
begin
select @szText = 'welcome 01'
Exec @rt = addLine @szText --調用addLine
select @szText = 'welcome 02'
Exec @rt = addLine @szText
end
exec sp_dropextendedproc 'SetFileName'
exec sp_dropextendedproc 'addLine'
dbcc SetFileName(free)
dbcc addLine(free)
動態連接庫的實現：這種動態連接庫和普通的有所不同。該動態連接庫要放入SQL的執行目錄下，或直接放到Window的System32目錄下,並重起SQL-Server
#include <windows.h>
#include <srv.h> //要加入這個.h文件
#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) && dwWt == 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_ICONINFORMATION);
}
#endif
}
BOOL WINAPI DllMain(HINSTANCE hinstDLL,DWORD fdwReason,LPVOID lpReserved)
{
return TRUE;
}
編譯完成後，把動態鏈接庫放到WINNT/System32目錄下,啟動SQL Server。我們可以打開SQL Server Query Analyzer調用存儲過程sp_testdll以測試其運行是否正確。
具體可參考SQL-Server的在線幫助。
筆者環境：win2000 professional + SQL-Server7.0(2000也可)
VC6.0+SP5+Platform SDK 20001.8
VC知識庫測試環境：win2000 professional + SQL-Server 7.0 + VC.Net
