SQL Server 2008 引入了更改跟蹤，這是一種輕量型解決方案，它為應用程序提供了一種有效的更改跟蹤機制。通常，若要使應用程序能夠查詢對數據庫中的數據所做的更改和訪問與這些更改相關的信息，應用程序開發人員必須實現自定義更改跟蹤機制。創建這些機制通常涉及多項工作，并且常常涉及使用觸發器、 timestamp 列和新表組合來存儲跟蹤信息，同時還會涉及使用自定義清除過程。
通過更改跟蹤，可以很容易地編寫同步數據的應用，下面是一個使用更改跟蹤實現單向數據同步的示例。
1 ． 建立示例環境
-- ====================================================
-- 測試的數據庫
USE master ;
GO
CREATE DATABASE DB_test ;
GO
-- 啟用更改跟蹤
ALTER DATABASE DB_test SET
CHANGE_TRACKING = ON (
AUTO_CLEANUP = ON , -- 打開自動清理選項
CHANGE_RETENTION = 1 HOURS -- 數據保存期為 1 時
);
ALTER DATABASE DB_test SET
ALLOW_SNAPSHOT_ISOLATION ON ; -- 允許在測試數據庫中使用 SNAPSHOT 事務隔離級別
GO
-- ====================================================
-- 測試的表
USE DB_test ;
GO
-- a. 同步的源表
CREATE TABLE dbo . tb_source (
pk_id int IDENTITY
PRIMARY KEY ,
col1 int ,
col2 varchar ( 10 ),
col3 nvarchar ( max ),
col4 xml
);
GO
-- 啟用更改跟蹤
ALTER TABLE dbo . tb_source
ENABLE CHANGE_TRACKING
WITH (
TRACK_COLUMNS_UPDATED = ON -- 記錄 UPDATE 的列信息
);
GO
-- b. 同步的目錄表
CREATE TABLE dbo . tb_Target (
pk_id int
PRIMARY KEY ,
col1 int ,
col2 varchar ( 10 ),
col3 nvarchar ( max ),
col4 xml
);
GO
-- 記錄同步情況的表
CREATE TABLE dbo . tb_Change_Tracking (
id int IDENTITY
PRIMARY KEY ,
object_name sysname
UNIQUE ,
last_sync_version bigint ,
last_update_date datetime
);
GO
2 ． 實現同步處理的存儲過程
-- ====================================================
-- 數據同步處理的存儲過程
USE DB_test ;
GO
-- 數據同步的存儲過程 - 同步未更新的數據
-- 單次更新，更新完成后退出
CREATE PROC dbo . p_SyncChangeData_tb_Srouce_Target
@last_sync_version bigint = NULL OUTPUT ,
@min_valid_version bigint = NULL OUTPUT
AS
SET NOCOUNT ON ;
-- ========================================
-- TRY...CATCH 中的標準事務處理模塊
-- a. 當前的事務數
DECLARE
@__trancount int ;
SELECT
@__trancount = @@TRANCOUNT ;
-- TRY...CATCH 處理
BEGIN TRY
-- ========================================
-- 源表信息
DECLARE
@object_name sysname ,
@object_id int ;
SELECT
@object_name = N'dbo.tb_source' ,
@object_id = OBJECT_ID ( @object_name );
-- ========================================
-- 最后一次同步的版本
IF @last_sync_version IS NULL
BEGIN
SELECT
@last_sync_version = last_sync_version
FROM dbo . tb_Change_Tracking
WHERE object_name = @object_name ;
IF @@ROWCOUNT = 0
BEGIN
SET @last_sync_version = CHANGE_TRACKING_MIN_VALID_VERSION ( @object_id );
INSERT dbo . tb_Change_Tracking (
object_name , last_sync_version )
VALUES (
@object_name , @last_sync_version );
END ;
END ;
-- ========================================
-- TRY...CATCH 中的標準事務處理模塊
-- b. 開啟事務 , 或者設置事務保存點
SET TRANSACTION ISOLATION LEVEL SNAPSHOT ; -- 使用快照隔離級別的事務
IF @__trancount = 0
BEGIN TRAN ;
ELSE
SAVE TRAN __TRAN_SavePoint ;
-- ========================================
-- 版本驗證
-- a. 驗證是否有數據變更 ( 如果上次同步的版本號 = 當前數據庫的最大版本號，則視為無數據變化 )
IF @last_sync_version = CHANGE_TRACKING_CURRENT_VERSION ()
GOTO lb_Return ;
-- b. 驗證同步的版本號是否有效 ( 如果上次同步的版本號 < 當前可用的最小版本號，則視為無效 )
IF @last_sync_version < CHANGE_TRACKING_MIN_VALID_VERSION ( @object_id )
BEGIN
SET @min_valid_version = CHANGE_TRACKING_MIN_VALID_VERSION ( @object_id );
GOTO lb_Return ;
END ;
-- c. 驗證同步的版本號是否有效 ( 如果上次同步的版本號 > 當前數據庫的最大版本號，則視為無效 )
IF @last_sync_version > CHANGE_TRACKING_CURRENT_VERSION ()
BEGIN
SET @last_sync_version = NULL;
GOTO lb_Return ;
END ;
-- ========================================
-- 同步數據
-- a. 插入
WITH
CHG AS (
SELECT
DATA .*
FROM dbo . tb_source DATA
INNER JOIN CHANGETABLE ( CHANGES dbo . tb_source , @last_sync_version ) CHG
ON CHG . pk_id = DATA . pk_id
WHERE CHG . SYS_CHANGE_OPERATION = N'I'
)
INSERT dbo . tb_Target
SELECT * FROM CHG ;
-- b. 刪除
WITH
CHG AS (
SELECT
CHG .*
FROM CHANGETABLE ( CHANGES dbo . tb_source , @last_sync_version ) CHG
WHERE CHG . SYS_CHANGE_OPERATION = N'D'
)
DELETE DATA
FROM dbo . tb_Target DATA
INNER JOIN CHG
ON CHG . pk_id = DATA . pk_id ;
-- c. 更新
WITH
COL AS (
lang
發表評論
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
評論