SQL 2008 Change Tracking 練習

CREATE DATABASE dbChangeTrack
GO
USE dbChangeTrack

CREATE TABLE tbTrack(PK INT IDENTITY(1,1) PRIMARY KEY,
c2 NVARCHAR(50),
c3 NVARCHAR(50)
)
GO

–啟動資料庫的 Change Tracking 功能
ALTER DATABASE dbChangeTrack
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)

–要記錄哪些資料欄位已更新的資訊到系統的變更追蹤資料表
ALTER TABLE tbTrack
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)
GO

–系統函數 CHANGETABLE 會傳回資料表在 last_sync_version 指定版本之後所有變更,
–所以我們自行撰寫的 AP 在每次同步後,要取得當下的版本,當作下次呼叫 ChangeTable 函數的起始點
DECLARE @last_synchronization_version BIGINT=CHANGE_TRACKING_CURRENT_VERSION()

–變更資料
INSERT tbTrack VALUES(‘A’,’A’)
INSERT tbTrack VALUES(‘B’,’B’)
INSERT tbTrack VALUES(‘C’,’C’)

–查閱自最後同步的版本以來之變更
SELECT
    CT.PK, CT.SYS_CHANGE_OPERATION,
    CT.SYS_CHANGE_COLUMNS, CT.SYS_CHANGE_CONTEXT
FROM
    CHANGETABLE(CHANGES tbTrack, @last_synchronization_version) AS CT

/*
做一些同步的動作,並記載最後同步的版本
*/
SET @last_synchronization_version =CHANGE_TRACKING_CURRENT_VERSION()

–在同一批的變更追蹤之間,已做了多次變更,只有最後的結果
UPDATE tbTrack SET c2=’AA’ WHERE PK=1
DELETE tbTrack WHERE PK=1

UPDATE tbTrack SET c2=’BB’ WHERE PK=2

SELECT
    CT.PK, CT.SYS_CHANGE_OPERATION,
    CT.SYS_CHANGE_COLUMNS, CT.SYS_CHANGE_CONTEXT
FROM
    CHANGETABLE(CHANGES tbTrack, @last_synchronization_version) AS CT

USE master
DROP DATABASE dbChangeTrack

執行結果:

image

3 Comments

  1. Unknown
    Posted 2008 年 09 月 07 日 at 05:13:43 | Permalink | 回覆

    Hi,Do you need advertising displays, advertisement screens, LCD digital signage and LCD signages? Please go Here:www.amberdigital.com.hk(Amberdigital).we have explored and developed the international market with professionalism. We have built a widespread marketing network, and set up a capable management team dedicated to provide beyond-expectation services to our customers.
    amberdigital Contact Us
    E-mail:sstar@netvigator.com
    website:www.amberdigital.com.hk
    alibaba:amberdigital.en.alibaba.com[dca

  2. Unknown
    Posted 2008 年 09 月 27 日 at 13:39:31 | Permalink | 回覆

    Hi,Do you have used LCDs, used flat screens and secondhand LCDs? Please go here:www.sstar-hk.com(Southern Stars).We are constantly buying re-usable LCD panels and working for LCD recycling.The re-usable panels go through strictly designed process of categorizing, checking, testing, repairing and refurbishing before they are re-used to make remanufactured LCD displays and TV sets.Due to our recent breakthrough in testing and repairing technology of LCD, we can improve the value for your LCD panels.
    website:www.sstar-hk.com[cabgdgefgaeadf]

  3. 小智~*
    Posted 2008 年 10 月 03 日 at 05:52:44 | Permalink | 回覆

    sql 2008之後你可以寫成 INSERT tbTrack VALUES(‘A’,’A’),(‘B’,’B’),(‘C’,’C’)

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: