我需要獲取資料庫中所有已更改的表，包括已更新/添加/洗掉的行 ID。
因此，如果我有表table1和table2，并且 ID 為 15 和 16 的table1行在 中被更改，并且 ID 為 200 和 201 的行在 中被更改table2，我想運行一個回傳的腳本：
----------- -------
| tableName | rowId |
----------- -------
| table1 | 15 |
| table1 | 16 |
| table2 | 200 |
| table2 | 201 |
----------- -------
我設法找到了這個腳本：
set nocount on;
-- We want to check for changes since the previous version
--declare @prevTrackingVersion int = INSERT_YOUR_PREV_VERSION_HERE
-- Comment out this line if you know the previous version
declare @prevTrackingVersion int = CHANGE_TRACKING_CURRENT_VERSION() - 1
-- Get a list of table with change tracking enabled
declare @trackedTables as table (name nvarchar(1000));
insert into @trackedTables (name)
select sys.tables.name from sys.change_tracking_tables
join sys.tables ON tables.object_id = change_tracking_tables.object_id
-- This will be the list of tables with changes
declare @changedTables as table (name nvarchar(1000));
-- For each table name in tracked tables
declare @tableName nvarchar(1000)
while exists(select top 1 * from @trackedTables)
begin
-- Set the current table name
set @tableName = (select top 1 name from @trackedTables order by name asc);
print @tableName
-- Determine if the table has changed since the previous version
declare @sql nvarchar(250)
declare @retVal int
set @sql = 'select @retVal = count(*) from changetable(changes ' @tableName ', ' cast(@prevTrackingVersion as varchar) ') as changedTable'
exec sp_executesql @sql, N'@retVal int output', @retVal output
print @retVal
if @retval > 0
begin
insert into @changedTables (name) select @tableName
end
-- Delete the current table name
delete from @trackedTables where name = @tableName;
end
select * from @changedTables;
但是，它只輸出被修改的表的名稱。
所以，我知道這個命令：
SELECT * FROM CHANGETABLE(CHANGES table1, 0) as CT;
輸出這個：
---------------------- ----
| SYS_CHANGE_OPERATION | ID |
---------------------- ----
| I | 15 |
| I | 16 |
---------------------- ----
在腳本中，使用了相同的命令：
set @sql = 'select @retVal = count(*) from changetable(changes ' @tableName ', ' cast(@prevTrackingVersion as varchar) ') as changedTable'
但它只獲取計數，然后檢查它是否大于 0（如果此表有任何修改）：
if @retval > 0
begin
insert into @changedTables (name) select @tableName
end
但是，如前所述，我不僅需要修改表，還需要修改行的 ID。
我覺得我需要加入，但我不確定如何解決這個問題。
謝謝
uj5u.com熱心網友回復：
你需要做的是——
- 找出所有跟蹤表及其主鍵。從 CHANGETABLE 回傳的結果包括跟蹤表的主鍵，而不是您想象的 ROWID。例如，如果表 t1 的 PK 定義在 t1.id 上，則 t1 的 CHANGETABLE 將回傳“id”列；如果表 t2 的 PK 在 (id1, id2) 上定義，則 t2 的 CHANGETABLE 將回傳“id1”和“id2”。
嘗試為一張表運行 CHANGETABLE 可以幫助您理解我在說什么 -
select *
from CHANGETABLE(CHANGES your_table, 0 /*track version*/) chg
為簡單起見，我假設您所有的跟蹤表都有單列 PK。您可以通過以下方式找到跟蹤表及其 PK -
select c.table_name
,k.column_name pk
from sys.change_tracking_tables t
,information_schema.table_constraints c
,information_schema.key_column_usage k
where object_name(t.object_id) = c.table_name
and c.constraint_type = 'PRIMARY KEY'
and c.table_name = k.table_name
and c.constraint_name = k.constraint_name
- 為每個跟蹤表運行 CHANGETABLE 并組合結果。
這是整個修改后的腳本 -
set nocount on;
-- We want to check for changes since the previous version
--declare @prevTrackingVersion int = INSERT_YOUR_PREV_VERSION_HERE
-- Comment out this line if you know the previous version
declare @prevTrackingVersion int CHANGE_TRACKING_CURRENT_VERSION() - 1
-- Get a list of table with change tracking enabled
declare @trackedTables as table (table_name nvarchar(100), pk nvarchar(100));
insert into @trackedTables (table_name, pk)
select c.table_name
,k.column_name pk
from sys.change_tracking_tables t
,information_schema.table_constraints c
,information_schema.key_column_usage k
where object_name(t.object_id) = c.table_name
and c.constraint_type = 'PRIMARY KEY'
and c.table_name = k.table_name
and c.constraint_name = k.constraint_name
-- This will be the list of changes
declare @changes as table (table_name varchar(100), pk varchar(100))
-- For each table name in tracked tables
declare @table_name nvarchar(100)
,@pk nvarchar(100)
while exists(select top 1 * from @trackedTables)
begin
-- Set the current table name
select top 1 @table_name = table_name, @pk = pk from @trackedTables order by table_name asc;
insert into @changes (table_name, pk)
exec ('select ''' @table_name ''', ' @pk ' from CHANGETABLE(CHANGES ' @table_name ', ' @prevTrackingVersion ') chg')
-- Delete the current table name
delete from @trackedTables where table_name = @table_name;
end
select * from @changes;
uj5u.com熱心網友回復：
您可以像這樣在架構中的每個表（或者更確切地說是您真正需要的表）上創建一個觸發器
CREATE TRIGGER TRX_TableName_ChangeLog ON TableName
AFTER INSERT,DELETE,UPDATE
AS
BEGIN
DECLARE @TABLE_NAME NVARCHAR(400)
SELECT @TABLE_NAME = OBJECT_NAME(parent_object_id)
FROM sys.objects
WHERE sys.objects.object_id = @@PROCID
INSERT INTO ChangeTableLog (TABLE_NAME, TABLE_ID)
SELECT @TABLE_NAME,INSERTED.ID
FROM INSERTED --or delete
END
觸發器的創建可以是動態的，如下所示：
DECLARE @TABLE_NAME nvarchar(200),@Trigger nvarchar(max)
DECLARE CURS CURSOR LOCAL FAST_FORWARD FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
OPEN CURS
FETCH NEXT FROM CURS INTO @TABLE_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Trigger =
'CREATE TRIGGER TRX_' @TABLE_NAME '_ChangeLog
ON ' @TABLE_NAME '
AFTER INSERT,DELETE,UPDATE
AS
BEGIN
DECLARE @TABLE_NAME NVARCHAR(400)
SELECT @TABLE_NAME = OBJECT_NAME(parent_object_id)
FROM sys.objects
WHERE sys.objects.object_id = @@PROCID
INSERT INTO ChangeTableLog (TABLE_NAME, TABLE_ID)
SELECT @TABLE_NAME,INSERTED.ID
FROM INSERTED
END'
EXEC(@Trigger)
FETCH NEXT FROM CURS INTO @TABLE_NAME
END
CLOSE CURS
DEALLOCATE CURS
就個人而言，我不喜歡使用觸發器，但毫無疑問它們很容易實作。我不知道您的系統的規模，但最好的情況是創建條目的服務還將在每個表中包含一個新列以保持更改或僅說明每個條目的執行日期。
uj5u.com熱心網友回復：
您可以使用資料庫觸發器
在此觸發器中，您可以訪問以管理資料庫中的許多操作，例如表更改等。此觸發器的示例如下：
CREATE TRIGGER trigger1
ON DATABASE
FOR DDL_TABLE_EVENTS
AS
DECLARE @data XML
SET @data = EVENTDATA()
BEGIN
SELECT @data
-- do what you whant with openxml SQL function
END
GO
@data 是一個 XML，其中包含您需要從更改中理解的所有內容。
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/495503.html