欄位對映 / Table Column Maps — ERP 中介資料交換平台

對映清單

同步範圍由 active 對映決定；無 active 對映則輪詢 no-op。

來源表	複本表	主鍵欄	欄位數	啟用	動作
dbo.COWEB01T101	dbo.SHP01T401	CompanyId	11	啟用	編輯
dbo.COWEB02T101	dbo.SHP01T402	SettingItemId	6	啟用	編輯
dbo.COWEB02T102	dbo.SHP01T403	SettingItemId,CompanyId	6	啟用	編輯
dbo.COWEB02T103	dbo.SHP01T404	FaqId	4	啟用	編輯
dbo.COWEB04T101	dbo.SHP01T101	ProductCategoryId	13	啟用	編輯
dbo.COWEB04T102	dbo.SHP01T102	ProductId	14	啟用	編輯
dbo.COWEB04T103	dbo.SHP01T103	ProductDetailId	7	啟用	編輯
dbo.COWEB04T104	dbo.SHP01T104	ProductCategoryId,ProductId	3	啟用	編輯
dbo.COWEB04T117	dbo.SHP01T117	ProductSpecTypeId	3	啟用	編輯
dbo.COWEB04T118	dbo.SHP01T118	ProductSpecId	5	啟用	編輯
dbo.COWEB04T201	dbo.SHP01T301	SeoMetaId	5	啟用	編輯

來源 CT 啟用 SQL（給 DBA）

依目前對映的來源表動態產生。App 無來源 DDL 權限，請把這段複製交給 DBA 在來源 DB 執行；腳本 idempotent、可重複跑。

/* 由 Hub 依目前對映自動產生：在來源 DB 啟用 Change Tracking。
   App 無來源 DDL 權限，請把本段交由 DBA 在來源 DB 執行；腳本 idempotent、可重複執行。 */

-- 1) DB 層級啟用
IF NOT EXISTS (SELECT 1 FROM sys.change_tracking_databases WHERE database_id = DB_ID(N'COWEB'))
    ALTER DATABASE [COWEB] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
GO

USE [COWEB];
GO

-- 2) 每張來源表啟用（必須有主鍵）
IF NOT EXISTS (SELECT 1 FROM sys.change_tracking_tables WHERE object_id = OBJECT_ID(N'dbo.COWEB01T101'))
    ALTER TABLE [dbo].[COWEB01T101] ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);
IF NOT EXISTS (SELECT 1 FROM sys.change_tracking_tables WHERE object_id = OBJECT_ID(N'dbo.COWEB02T101'))
    ALTER TABLE [dbo].[COWEB02T101] ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);
IF NOT EXISTS (SELECT 1 FROM sys.change_tracking_tables WHERE object_id = OBJECT_ID(N'dbo.COWEB02T102'))
    ALTER TABLE [dbo].[COWEB02T102] ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);
IF NOT EXISTS (SELECT 1 FROM sys.change_tracking_tables WHERE object_id = OBJECT_ID(N'dbo.COWEB02T103'))
    ALTER TABLE [dbo].[COWEB02T103] ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);
IF NOT EXISTS (SELECT 1 FROM sys.change_tracking_tables WHERE object_id = OBJECT_ID(N'dbo.COWEB04T101'))
    ALTER TABLE [dbo].[COWEB04T101] ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);
IF NOT EXISTS (SELECT 1 FROM sys.change_tracking_tables WHERE object_id = OBJECT_ID(N'dbo.COWEB04T102'))
    ALTER TABLE [dbo].[COWEB04T102] ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);
IF NOT EXISTS (SELECT 1 FROM sys.change_tracking_tables WHERE object_id = OBJECT_ID(N'dbo.COWEB04T103'))
    ALTER TABLE [dbo].[COWEB04T103] ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);
IF NOT EXISTS (SELECT 1 FROM sys.change_tracking_tables WHERE object_id = OBJECT_ID(N'dbo.COWEB04T104'))
    ALTER TABLE [dbo].[COWEB04T104] ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);
IF NOT EXISTS (SELECT 1 FROM sys.change_tracking_tables WHERE object_id = OBJECT_ID(N'dbo.COWEB04T117'))
    ALTER TABLE [dbo].[COWEB04T117] ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);
IF NOT EXISTS (SELECT 1 FROM sys.change_tracking_tables WHERE object_id = OBJECT_ID(N'dbo.COWEB04T118'))
    ALTER TABLE [dbo].[COWEB04T118] ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);
IF NOT EXISTS (SELECT 1 FROM sys.change_tracking_tables WHERE object_id = OBJECT_ID(N'dbo.COWEB04T201'))
    ALTER TABLE [dbo].[COWEB04T201] ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);
GO

-- 3) 授權 Hub 讀取帳號（非 sysadmin 才需要；sa/db_owner 可略過）
GRANT VIEW CHANGE TRACKING ON [dbo].[COWEB01T101] TO [COWEB];
GRANT SELECT ON [dbo].[COWEB01T101] TO [COWEB];
GRANT VIEW CHANGE TRACKING ON [dbo].[COWEB02T101] TO [COWEB];
GRANT SELECT ON [dbo].[COWEB02T101] TO [COWEB];
GRANT VIEW CHANGE TRACKING ON [dbo].[COWEB02T102] TO [COWEB];
GRANT SELECT ON [dbo].[COWEB02T102] TO [COWEB];
GRANT VIEW CHANGE TRACKING ON [dbo].[COWEB02T103] TO [COWEB];
GRANT SELECT ON [dbo].[COWEB02T103] TO [COWEB];
GRANT VIEW CHANGE TRACKING ON [dbo].[COWEB04T101] TO [COWEB];
GRANT SELECT ON [dbo].[COWEB04T101] TO [COWEB];
GRANT VIEW CHANGE TRACKING ON [dbo].[COWEB04T102] TO [COWEB];
GRANT SELECT ON [dbo].[COWEB04T102] TO [COWEB];
GRANT VIEW CHANGE TRACKING ON [dbo].[COWEB04T103] TO [COWEB];
GRANT SELECT ON [dbo].[COWEB04T103] TO [COWEB];
GRANT VIEW CHANGE TRACKING ON [dbo].[COWEB04T104] TO [COWEB];
GRANT SELECT ON [dbo].[COWEB04T104] TO [COWEB];
GRANT VIEW CHANGE TRACKING ON [dbo].[COWEB04T117] TO [COWEB];
GRANT SELECT ON [dbo].[COWEB04T117] TO [COWEB];
GRANT VIEW CHANGE TRACKING ON [dbo].[COWEB04T118] TO [COWEB];
GRANT SELECT ON [dbo].[COWEB04T118] TO [COWEB];
GRANT VIEW CHANGE TRACKING ON [dbo].[COWEB04T201] TO [COWEB];
GRANT SELECT ON [dbo].[COWEB04T201] TO [COWEB];
GO

-- 4) 驗證
SELECT CHANGE_TRACKING_CURRENT_VERSION() AS CurrentVersion;
SELECT SCHEMA_NAME(t.schema_id) + N'.' + t.name AS EnabledTable
FROM sys.change_tracking_tables ctt JOIN sys.tables t ON t.object_id = ctt.object_id;