ทำ Database change tracking โดยไม่ต้องเขียน code (MSSQL Change Tracking)

ย้ายไปอยู่ที่ : developer-today.com

https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/work-with-change-tracking-sql-server?view=sql-server-ver15

จากโจทย์ ที่มีคือ ลูกค้าต้องการให้พัฒนาระบบ เก็บข้อมูลที่ต้องการ เชื่อโยงข้อมูลไปให้อีกระบบ แบบ realtime หากข้อมูลมีการเปลี่ยนแปลง

วิธีทำ ถ้าคิดแบบบ้านๆ ก็สร้าง trigger ตรวจสอบหากข้อมูลมีการ Insert,Update,Delete ให้ส่งค่าจาก database เราไปให้ database ปลายทาง แต่ว่าโลกแห่งความจริงมันไม่ง่ายแบบนั้น เพราะถ้าหากเรามี field ใน table สัก 40 filed แต่ เราสนใจการเปลี่ยนแปลงแค่ บาง field เท่านั้น หมายความว่า ถ้าเราส่งข้อมูลไปให้ อีก database ทุกครั้งที่มีการเปลี่ยนแปง จำนวน transactin มากมายมหาศาล ก็จะถูกส่งไปโดยไม่จำเป็น

เรามาดูวิธีแก้กัน วิธีที่ว่าคือการเปิดใช้งาน Change Tracking ของ Database (วิธีนี้สำหรับ MSSQL 2008 ขึ้นไปนะครับ) เมื่อเปิดใช้งานแล้วระบบ จะทำการเก็บการเปลี่ยนแปลงของ column ตาม version เราก็เพียงแค่ตรวจสอบว่า column ที่มีการเปลี่ยนแปลงเป็น column ที่เราสนใจหรือไม่ ถ้าใช้ก็ ส่งข้อมูลไปโลดดด ถ้าไม่ใช่ก็ ปล่อยผ่าน~~ มาเริ่มกันเลย!!!

ขั้นตอนมีดังนี้
1. เปิด Change Tracking ที่ Database
2. เปิด Change Tracking ใน table ที่ต้องการจะ tracking
3. ทดสอบ

1. ปิด Change Tracking ที่ Database

USE master
GO
ALTER DATABASE [Training]
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 15 DAYS, AUTO_CLEANUP = ON)

อธิบายคำสั่งคร่าวๆ
จาก Script ด้านบนเป็นการเปิด database change tracking ที่ database ชื่อ Training ให้เก็บข้อมูลการเปลี่ยนแปลง 15 วัน และ ให้ database auto ลบข้อมูล อัตโนมัติ

2. เปิด Change Tracking ใน table ที่ต้องการจะ tracking

USE Training
GO
ALTER TABLE dbo.Users
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)

อธิบาย
ใน script เป็นการเปิด tracking ใน table dbo.Users (table จำเป็นจะต้องมี Primary Key นะจ๊ะ)

3. ทดสอบ

INSERT INTO [Users] (UserName,[Password],Email,Firstname,Lastname,Phone,JobPositionID,CompanyID,CreatedBy,CreatedDate,UpdatedBy,UpdatedDate)
VALUES ('Test 01','1234','test01@mail.com','test','01','0879999888',1,1,'script',getdate(),'script',getdate())

มาดูผลกัน

DECLARE  @ChangeVersion bigint =0
SELECT * FROM CHANGETABLE (CHANGES Users, @ChangeVersion) ch

จะเห็นว่า ตัว tracking change ของเราทำงานได้แล้ว โดยดูจาก function CHANGETABLE โดยที่ แตละ field มี ความหมายด
SYS_CHANGE_VERSION : เลข version ของ change ปัจจุบัน ซึ่งจะ เพิ่มขึ้นตาม commit transaction
SYS_CHANGE_CREATION_VERSION : เลขของ version ที่ Insert ข้อมูล
SYS_CHANGE_OPERATION : ประเภทของ Change ชึ่งมี I = Insert, U = Update และ D= Delete
SYS_CHANGE_COLUMNS: รายชื่อ column ที่มีการเปลี่ยนแปลเป็น binary
SYS_CHANGE_COLUMNS : คือข้อมูลที่เราใส่เข้าไปในขณะทำ transaction คู่กับคำสั่ง WITH CHANGE_TRACKING_CONTEXT ( context )

หากเราทำการ update ข้อมูล ก็สามารถตรวจสอบได้จาก script

DECLARE  @ChangeVersion bigint 
SET @ChangeVersion =CHANGE_TRACKING_CURRENT_VERSION()-1
DECLARE @CheckChangeColumnId int =COLUMNPROPERTY(OBJECT_ID('Users'), 'Phone', 'ColumnId')
SELECT CT.*,[IsChange?]=CHANGE_TRACKING_IS_COLUMN_IN_MASK(@CheckChangeColumnId,SYS_CHANGE_COLUMNS)FROM CHANGETABLE (CHANGES Users,@ChangeVersion) CT

อธิยบาย
ผมได้ทำการ update ข้อมูล Phone ไปใน table User แล้วตรวจสอบจาก CHNAGETABLE ว่ามีการเปลี่ยนแปลงไปจาก version ปัจจุบันหรือไม่ โดย ดูข้อมูลจาก Version ก่อนหน้า ในที่นี้คือ version ปัจจุบันคือ 6 ผมดึงข้อมูลการเปลี่ยนแปลงจาก version 5 ออกมาตรวจสอบ จาก คำสั่ง CHANGE_TRACKING_IS_COLUMN_IN_MASK ซึ่งคำสั่งนี้จำเป็นต้องใช้ ColumnID , ในการ compare ค่าใน SYS_CHANGE_COLUMNS ผมจึ้งไปดึงค่า columnID จาก คำสั่ง COLUMNPROPERTY ซึ่งค่าที่ได้จาก CHANGE_TRACKING_IS_COLUMN_IN_MASK จะมี 2 ค่าคือ 1 =มี Column อยู่ใน list การเปลี่ยนแปลง, 0 = ไม่มี column อยู่ใน list

เพียงเท่านี้เราก็สามารถตรวจสอบการเปลี่ยนแปลงของ data ได้อย่างง่ายดาย

จบ จร้า