Início / dba / Perguntas / 335647
Accepted
Hannah Vernon
Asked: 2024-02-09 02:51:11 +0800 CST

Cláusula OUTPUT retornando 0 para valor de identidade recém-inserido devido ao gatilho INSTEAD OF

  • 772

Considere o seguinte código de exemplo mínimo, completo e verificável (veja dbfiddle aqui ):

CREATE TABLE [dbo].[test]
(
      [i] bigint NOT NULL 
        identity(1,1) 
        PRIMARY KEY CLUSTERED
    , [d] varchar(10) NOT NULL
);
GO

Com um INSTEAD OF INSERT, UPDATEgatilho:

CREATE TRIGGER [dbo_test_trigger]
ON [dbo].[test]
INSTEAD OF INSERT, UPDATE
AS
BEGIN
    IF ROWCOUNT_BIG() = 0 RETURN;

    SET NOCOUNT ON;

    MERGE INTO [dbo].[test] [target]
    USING [inserted] [source] ON [target].[i] = [source].[i]
    WHEN NOT MATCHED THEN
        INSERT
        (
            [d]
        )
        VALUES 
        (
            [source].[d]
        )
    WHEN MATCHED THEN 
        UPDATE
        SET [target].[d] = [source].[d];
END;
GO

Estou executando um insert na tabela, na esperança de obter o valor da identidade inserida, porém o valor retornado é 0:

DECLARE @output TABLE
(
      [i] bigint NOT NULL
    , [d] varchar(10) NOT NULL
);

INSERT INTO [dbo].[test]
(
    [d]
)
OUTPUT 
      [inserted].[i]
    , [inserted].[d]
INTO @output 
(
      [i]
    , [d]
) 
VALUES ('test');

/* shows [i] is 0 */
SELECT *
FROM @output;

/* shows [i] is 1 */
SELECT *
FROM [dbo].[test];

Os resultados são:

eu d
0 teste

e

eu d
1 teste

O resultado desejado seria que ambos os conjuntos de resultados correspondessem, mas isso não acontece.

O que estou fazendo de errado?

Eu vi isso, no entanto, que parece bem diferente, já que não estou usando nenhuma visualização. O gatilho está na mesa no meu exemplo.

sql-server

2 respostas

  1. Best Answer

    O comportamento é confuso e mal documentado.

    Costumava ser um pouco melhor documentado em INSTEAD OF INSERT Triggers (link para documentos do 2008 R2):

    insira a descrição da imagem aqui

    A explicação é que no momento em que os valores são capturados – antes do gatilho ser acionado – o SQL Server não pode saber quantas linhas serão realmente inseridas pelo gatilho. Ele não atribui valores de identidade antecipadamente e de alguma forma tenta combiná-los quando os resultados do gatilho são conhecidos.

    A citação:

    os resultados retornados são gerados como se INSERT, UPDATE ou DELETE tivessem realmente ocorrido

    transmite o conceito geral. Isso não significa que os resultados retornados sejam exatamente iguais em todos os aspectos , como se a operação subjacente tivesse ocorrido. Isso simplesmente não é possível em todas as situações.

    O parágrafo anterior diz (ênfase adicionada):

    As colunas retornadas de OUTPUT refletem os dados como estão após a conclusão da instrução INSERT, UPDATE ou DELETE, mas antes da execução dos gatilhos .

    Nenhum valor de identidade é atribuído antes das INSTEAD OFações ocorrerem, portanto eles não podem estar presentes no OUTPUTconjunto.

    Pode ser útil lembrar que a operação inicial (INSERT neste exemplo) ocorre em uma tabela temporária oculta, apesar de estar rotulada nos planos de execução como a própria tabela de destino. Mais informações em meu artigo Coisas interessantes sobre gatilhos INSTEAD OF .

    Os gatilhos INSTEAD OF são diferentes porque esse tipo de gatilho DML substitui completamente a ação acionada. As pseudotabelas inseridas e excluídas agora representam alterações que teriam sido feitas se a instrução de acionamento tivesse sido realmente executada. O versionamento de linha não pode ser usado para esses gatilhos porque nenhuma modificação ocorreu, por definição. Então, se não estiver usando versões de linha, como o SQL Server faz isso?

    A resposta é que o SQL Server modifica o plano de execução para a instrução DML de acionamento quando existe um gatilho INSTEAD OF. Em vez de modificar diretamente as tabelas afetadas, o plano de execução grava informações sobre as alterações em uma tabela de trabalho oculta. Esta tabela de trabalho contém todos os dados necessários para realizar as alterações originais, o tipo de modificação a ser realizada em cada linha (excluir ou inserir), bem como qualquer informação necessária no gatilho de uma cláusula OUTPUT.

    • 10

  2. Está se comportando conforme documentado.

    Citação relevante: cláusula OUTPUT (Transact-SQL)

    Para gatilhos INSTEAD OF, os resultados retornados são gerados como se INSERT, UPDATE ou DELETE tivessem realmente ocorrido, mesmo que nenhuma modificação ocorra como resultado da operação do gatilho.

    Demonstração atualizada:

    DROP TABLE IF EXISTS dbo.Test
CREATE TABLE [dbo].[test]
(
      [i] bigint NOT NULL 
        identity(1,1) 
        PRIMARY KEY CLUSTERED
    , [d] varchar(10) NOT NULL
);
GO

INSERT INTO [dbo].[test]
(
    [d]
)
VALUES ('seed');

go
CREATE TRIGGER [dbo_test_trigger]
ON [dbo].[test]
INSTEAD OF INSERT, UPDATE
AS
BEGIN
    IF ROWCOUNT_BIG() = 0 RETURN;

    SET NOCOUNT ON;

    INSERT INTO [dbo].[test] 
    OUTPUT Inserted.* , 'trigger', IDENT_CURRENT('dbo.test') INTO #output
    SELECT d FROM Inserted
    --WHERE 1 = 0

END;
GO

DROP TABLE IF EXISTS #output
CREATE table #output
(
    SeqNo int IDENTITY (1,1)
    ,  [i] bigint NOT NULL
    , [d] varchar(10) NOT NULL
    , type varchar(20)
    , identCurrent bigint
);

INSERT INTO [dbo].[test]
(
    [d]
)
OUTPUT 
      [inserted].[i]
    , [inserted].[d]
    , 'outer'
    , IDENT_CURRENT('dbo.test')
INTO #output 
VALUES ('test');

SELECT *
FROM #output;

SELECT *, IDENT_CURRENT('dbo.test') AS identCurrent
FROM [dbo].[test]

    Resultado insira a descrição da imagem aqui

    Como a tabela temporária é visível no escopo aninhado, posso ver que a primeira saída é a externa (mesmo antes de o gatilho ser chamado.

    Se você descomentar, WHERE 1 = 0poderá ver que os valores pretendidos são inseridos de qualquer maneira.

    • 4

relate perguntas