CHANGETABLE (Transact-SQL)

Artigo
06/18/2013

Retorna as informações de controle de alterações de uma tabela. Você pode usar essa instrução para retornar todas as alterações de uma tabela ou as informações de controle de alterações de uma linha específica.

Ícone de vínculo de tópico Convenções de sintaxe Transact-SQL

Sintaxe

CHANGETABLE (
    { CHANGES table , last_sync_version
    | VERSION table , <primary_key_values> } )
[AS] table_alias [ ( column_alias [ ,...n ] )

<primary_key_values> ::=
( column_name [ , ...n ] ) , ( value [ , ...n ] )

Argumentos

CHANGES table , last_sync_version
Retorna informações de rastreamento para todas as alterações efetuadas em uma tabela desde a versão especificada por last_sync_version.
- table
  É a tabela definida pelo usuário na qual as alterações controladas são obtidas. O controle de alterações deve ser ativado na tabela. Um nome de tabela de uma, duas, três ou quatro partes pode ser usado. O nome da tabela pode ser um sinônimo para a tabela.
- last_sync_version
  Quando obtém alterações, o aplicativo chamador deve especificar o ponto a partir do qual são necessárias alterações. last_sync_version especifica esse ponto. A função retorna informações de todas as linhas que foram alteradas desde essa versão. O aplicativo faz uma consulta para receber alterações com uma versão maior que last_sync_version.
  
  Normalmente, antes de obter as alterações, o aplicativo chama CHANGE_TRACKING_CURRENT_VERSION() para obter a versão que será utilizada na próxima vez que for necessário fazer alterações. Por isso, o aplicativo não precisa interpretar ou entender o valor real.
  
  Como last_sync_version é obtido pelo aplicativo chamador, o aplicativo tem que manter o valor. Se o aplicativo perder este valor, ele deverá reinicializar os dados.
  
  last_sync_version é bigint. O valor deve ser escalar. Uma expressão causará um erro de sintaxe.
  
  Se o valor for NULL, todas as alterações efetuadas serão retornadas.
  
  last_sync_version deve ser validado para que não fique muito defasado, visto que algumas ou todas as informações de alteração podem ter sido apagadas de acordo com o período de retenção configurado para o banco de dados. Para obter mais informações, consulte CHANGE_TRACKING_MIN_VALID_VERSION (Transact-SQL) e Opções ALTER DATABASE SET (Transact-SQL).
VERSION table, { <primary_key_values> }
Retorna as informações de controle de alterações mais recentes de uma linha especificada. Os valores de chave primária devem identificar a linha. <primary_key_values> identifica as colunas de chave primária e especifica os valores. Os nomes de coluna de chave primária podem ser especificados em qualquer ordem.
- Table
  É a tabela definida pelo usuário na qual devem ser obtidas as informações de controle de alterações. O controle de alterações deve ser ativado na tabela. Um nome de tabela de uma, duas, três ou quatro partes pode ser usado. O nome da tabela pode ser um sinônimo para a tabela.
- column_name
  Especifica o nome de coluna/colunas de chave primária. Podem ser especificados vários nomes de coluna em qualquer ordem.
- Value
  É o valor da chave primária. Se houver várias colunas de chaves primárias, os valores deverão ser especificados na mesma ordem em que as colunas aparecem na lista column_name.
[AS] table_alias [ (column_alias [ ,...n ] ) ]
Fornece nomes para os resultados que são retornados por CHANGETABLE.
- table_alias
  É o alias da tabela de que é retornada por CHANGETABLE. table_alias é obrigatório deve ser um identificador válido.
- column_alias
  É um alias de coluna opcional ou lista de aliases de coluna opcional para as colunas que são retornadas por CHANGETABLE. Isso permite que os nomes de colunas sejam personalizados caso haja nomes duplicados nos resultados.

Tipos de retorno

table

Valores de retorno

CHANGETABLE CHANGES

Quando CHANGES é especificado, são retornadas zero ou mais linhas que têm as colunas a seguir.

SYS_CHANGE_VERSION

bigint

Valor de versão associado à última alteração efetuada na linha

SYS_CHANGE_CREATION_VERSION

bigint

Valores de versão associados à última operação de inserção.

SYS_CHANGE_OPERATION

nchar(1)

Especifica o tipo de alteração:

U = Atualizar

I = Inserir

D = Excluir

SYS_CHANGE_COLUMNS

varbinary(4100)

Lista as colunas alteradas desde a last_sync_version (a linha de base).

Observação
Colunas computadas nunca são listadas como alteradas.

O valor será NULL quando qualquer uma das condições a seguir for verdadeira:

O controle de alterações da coluna não está habilitado.
A operação é de inserção ou exclusão.
Todas as colunas de chave não primária foram atualizadas em uma operação. Este valor binário não deve ser interpretado diretamente. Em vez disso, para interpretá-lo, use CHANGE_TRACKING_IS_COLUMN_IN_MASK ().

SYS_CHANGE_CONTEXT

varbinary(128)

Altere as informações de contexto que podem ser especificadas opcionalmente usando a cláusula WITH como parte de uma instrução INSERT, UPDATE ou DELETE.

Igual às colunas de tabela de usuário

Os valores de chave primária da tabela controlada. Esses valores identificam exclusivamente cada linha da tabela do usuário.

CHANGETABLE VERSION

Quando VERSION é especificado, uma linha que tem as seguintes colunas é retornada.

Nome da coluna	Tipo de dados	Descrição
SYS_CHANGE_VERSION	bigint	Valor de versão de alteração atual associado à linha. O valor será NULL se uma alteração não tiver sido efetuada por um período maior que o de retenção do controle de alterações ou se a linha não tiver sido alterada desde que o controle de alterações foi habilitado.
SYS_CHANGE_CONTEXT	varbinary(128)	Altere as informações de contexto que podem opcionalmente ser especificadas usando a cláusula WITH como parte de uma instrução INSERT, UPDATE ou DELETE.
<valor de coluna de chave primária>	Igual às colunas de tabela de usuário	Os valores de chave primária da tabela controlada. Esses valores identificam exclusivamente cada linha da tabela do usuário.

SYS_CHANGE_VERSION

bigint

Valor de versão de alteração atual associado à linha.

O valor será NULL se uma alteração não tiver sido efetuada por um período maior que o de retenção do controle de alterações ou se a linha não tiver sido alterada desde que o controle de alterações foi habilitado.

SYS_CHANGE_CONTEXT

varbinary(128)

Altere as informações de contexto que podem opcionalmente ser especificadas usando a cláusula WITH como parte de uma instrução INSERT, UPDATE ou DELETE.

Igual às colunas de tabela de usuário

Os valores de chave primária da tabela controlada. Esses valores identificam exclusivamente cada linha da tabela do usuário.

Comentários

Em geral, a função CHANGETABLE é usada na cláusula FROM de uma consulta como se fosse uma tabela.

CHANGETABLE(CHANGES...)

Para obter dados de linha para linhas novas ou modificadas, una o conjunto de resultados à tabela do usuário utilizando as colunas de chave primária. Somente uma linha é retornada para cada linha na tabela do usuário que foi alterada, mesmo se tiverem sido efetuadas várias alterações na mesma linha desde o valor da last_sync_version.

Alterações de coluna de chave primária nunca são marcadas como atualizações. Se um valor de chave primária for alterado, será considerado como excluído do valor antigo e inserido no valor novo.

Se você excluir uma linha e depois inserir uma linha que tenha a chave primária antiga, a alteração será vista como uma atualização em todas as colunas da linha.

Os valores retornados para as colunas SYS_CHANGE_OPERATION e SYS_CHANGE_COLUMNS são relativos à versão de linha de base (last_sync_version) especificada. Por exemplo, se uma operação de inserção tiver sido feita na versão 10 e uma operação de atualização, na versão 15, e se a linha de base da last_sync_version for 12, uma atualização será relatada. Se o valor da last_sync_version for 8, uma inserção será informada. SYS_CHANGE_COLUMNS nunca informará colunas computadas como tendo sido atualizadas.

Geralmente, todas as operações que inserem, atualizam ou excluem dados em tabelas de usuário são controladas, inclusive a instrução MERGE.

As seguintes operações que afetam dados de tabela de usuário não são controladas:

Executando a instrução UPDATETEXT

Esta instrução é preterida e será removida em uma versão futura do SQL Server. No entanto, as alterações feitas com a cláusula .WRITE da instrução UPDATE são controladas.
Excluindo linhas usando TRUNCATE TABLE

Quando uma tabela está truncada, as informações do controle de alterações, associadas à tabela, são redefinidas como se o controle de alterações tivesse acabado de ser habilitado na tabela. Um aplicativo cliente deverá sempre validar sua última versão sincronizada. A validação falhará se a tabela foi truncada.

CHANGETABLE(VERSION...)

Um conjunto de resultados vazio será retornado se uma chave primária inexistente for especificada.

O valor de SYS_CHANGE_VERSION poderá ser NULL se nenhuma alteração tiver sido efetuada por um período maior que o de retenção (por exemplo, a limpeza removeu as informações de alteração) ou se a linha não tiver sido alterada desde que o controle de alterações foi habilitado para a tabela.

Permissões

Requer as seguintes permissões na tabela especificada pelo valor table para obter informações de controle de alterações:

Permissão SELECT nas colunas de chave primária
VIEW CHANGE TRACKING

Exemplos

A.Retornando linhas para uma sincronização inicial de dados

O exemplo a seguir mostra como obter dados para uma sincronização inicial dos dados da tabela. A consulta retorna todos os dados de linha e suas versões associadas. Você pode inserir ou adicionar esses dados ao sistema que conterá os dados sincronizados.

-- Get all current rows with associated version
SELECT e.[Emp ID], e.SSN, e.FirstName, e.LastName,
    c.SYS_CHANGE_VERSION, c.SYS_CHANGE_CONTEXT
FROM Employees AS e
CROSS APPLY CHANGETABLE 
    (VERSION Employees, ([Emp ID], SSN), (e.[Emp ID], e.SSN)) AS c;

B.Listando todas as alterações efetuadas desde uma versão específica

O exemplo a seguir lista todas as alterações feitas em uma tabela desde a versão especificada (@last_sync_version). [Emp ID] e SSN são colunas em uma chave primária composta.

DECLARE @last_sync_version bigint;
SET @last_sync_version = <value obtained from query>;
SELECT [Emp ID], SSN,
    SYS_CHANGE_VERSION, SYS_CHANGE_OPERATION,
    SYS_CHANGE_COLUMNS, SYS_CHANGE_CONTEXT 
FROM CHANGETABLE (CHANGES Employees, @last_sync_version) AS C;

C.Obtendo todos os dados alterados para uma sincronização

O exemplo a seguir mostra como você pode obter todos os dados alterados. Essa consulta une as informações de controle de alterações à tabela de usuário de forma que as informações da tabela de usuário sejam retornadas. Um LEFT OUTER JOIN é usado de forma que uma linha seja retornada para as linhas excluídas.

-- Get all changes (inserts, updates, deletes)
DECLARE @last_sync_version bigint;
SET @last_sync_version = <value obtained from query>;
SELECT e.FirstName, e.LastName, c.[Emp ID], c.SSN,
    c.SYS_CHANGE_VERSION, c.SYS_CHANGE_OPERATION,
    c.SYS_CHANGE_COLUMNS, c.SYS_CHANGE_CONTEXT 
FROM CHANGETABLE (CHANGES Employees, @last_sync_version) AS c
    LEFT OUTER JOIN Employees AS e
        ON e.[Emp ID] = c.[Emp ID] AND e.SSN = c.SSN;

D.Detectando conflitos usando CHANGETABLE (VERSION...)

O exemplo a seguir mostra como atualizar uma linha apenas se ela não tiver sido alterada desde a última sincronização. O número de versão da linha específica é obtido usando CHANGETABLE. Se a linha tiver sido atualizada, as alterações não serão efetuadas e a consulta retornará informações sobre a alteração mais recente efetuada na linha.

-- @last_sync_version must be set to a valid value
UPDATE
    SalesLT.Product
SET
    ListPrice = @new_listprice
FROM
    SalesLT.Product AS P
WHERE
    ProductID = @product_id AND
    @last_sync_version >= ISNULL (
        (SELECT CT.SYS_CHANGE_VERSION FROM 
            CHANGETABLE(VERSION SalesLT.Product,
            (ProductID), (P.ProductID)) AS CT),
        0);