Sincronização usando gatilhos

1. O gatilho segue o princípio ACID do banco de dados relacional? Existe alguma chance de uma inserção ser confirmada, mas o gatilho falhar?

Esta pergunta é parcialmente respondida em uma pergunta relacionada à qual você vinculou. O código do gatilho é executado no mesmo contexto transacional da instrução DML que o fez disparar, preservando a parte atômica dos princípios ACID que você mencionou. A instrução de acionamento e o código do acionador são bem-sucedidos ou falham como uma unidade.

As propriedades ACID também garantem que toda a transação (incluindo o código do acionador) deixará o banco de dados em um estado que não viola nenhuma restrição explícita ( Consistente ) e quaisquer efeitos consolidados recuperáveis ​​sobreviverão a uma falha do banco de dados ( Durável ).

A menos que a transação circundante (talvez implícita ou de confirmação automática) esteja em execução no SERIALIZABLEnível de isolamento , a propriedade Isolated não é garantida automaticamente. Outra atividade de banco de dados simultânea pode interferir na operação correta de seu código de gatilho. Por exemplo, o saldo da conta pode ser alterado por outra sessão depois de lê-lo e antes de atualizá-lo - uma clássica condição de corrida.

2. Minhas declarações IF e UPDATE parecem estranhas. Existe alguma maneira melhor de atualizar a linha [Conta] correta?

Existem razões muito boas para a outra pergunta à qual você vinculou não oferecer nenhuma solução baseada em gatilho. O código de gatilho projetado para manter uma estrutura desnormalizada sincronizada pode ser extremamente complicado de acertar e testar corretamente. Mesmo pessoas muito avançadas do SQL Server com muitos anos de experiência lutam com isso.

Manter um bom desempenho ao mesmo tempo em que preserva a exatidão em todos os cenários e evita problemas como impasses adiciona dimensões extras de dificuldade. Seu código de gatilho está longe de ser robusto e atualiza o saldo de todas as contas , mesmo que apenas uma única transação seja modificada. Existem todos os tipos de riscos e desafios com uma solução baseada em gatilho, o que torna a tarefa profundamente inadequada para alguém relativamente novo nessa área de tecnologia.

Para ilustrar alguns dos problemas, mostro alguns exemplos de código abaixo. Esta não é uma solução rigorosamente testada (os gatilhos são difíceis!) e não estou sugerindo que você a use como outra coisa senão um exercício de aprendizado. Para um sistema real, as soluções não-gatilho têm benefícios importantes, portanto, você deve revisar cuidadosamente as respostas para a outra pergunta e evitar completamente a ideia do gatilho.

Tabelas de exemplo

CREATE TABLE dbo.Accounts
(
    AccountID integer NOT NULL,
    Balance money NOT NULL,

    CONSTRAINT PK_Accounts_ID
    PRIMARY KEY CLUSTERED (AccountID)
);

CREATE TABLE dbo.Transactions
(
    TransactionID integer IDENTITY NOT NULL,
    AccountID integer NOT NULL,
    Amount money NOT NULL,

    CONSTRAINT PK_Transactions_ID
    PRIMARY KEY CLUSTERED (TransactionID),

    CONSTRAINT FK_Accounts
    FOREIGN KEY (AccountID)
    REFERENCES dbo.Accounts (AccountID)
);

prevenirTRUNCATE TABLE

Gatilhos não são acionados por TRUNCATE TABLE. A tabela vazia a seguir existe apenas para evitar que a Transactionstabela seja truncada (ser referenciada por uma chave estrangeira evita o truncamento da tabela):

CREATE TABLE dbo.PreventTransactionsTruncation
(
    Dummy integer NULL,

    CONSTRAINT FK_Transactions
    FOREIGN KEY (Dummy)
    REFERENCES dbo.Transactions (TransactionID),

    CONSTRAINT CHK_NoRows
    CHECK (Dummy IS NULL AND Dummy IS NOT NULL)
);

Definição de gatilho

O código de gatilho a seguir garante que apenas as entradas de conta necessárias sejam mantidas e usa SERIALIZABLEsemântica lá. Como um efeito colateral desejável, isso também evita os resultados incorretos que podem resultar se um nível de isolamento de controle de versão de linha estiver em uso. O código também evita a execução do código do gatilho se nenhuma linha for afetada pela instrução de origem. A tabela temporária e RECOMPILEa dica são usadas para evitar problemas no plano de execução do acionador causados ​​por estimativas de cardinalidade imprecisas:

CREATE TRIGGER dbo.TransactionChange ON dbo.Transactions 
AFTER INSERT, UPDATE, DELETE 
AS
BEGIN
IF @@ROWCOUNT = 0 OR
    TRIGGER_NESTLEVEL
    (
        OBJECT_ID(N'dbo.TransactionChange', N'TR'),
        'AFTER', 
        'DML'
    ) > 1 
    RETURN;

    SET NOCOUNT, XACT_ABORT ON;

    CREATE TABLE #Delta
    (
        AccountID integer PRIMARY KEY,
        Amount money NOT NULL
    );

    INSERT #Delta
        (AccountID, Amount)
    SELECT 
        InsDel.AccountID,
        Amount = SUM(InsDel.Amount)
    FROM 
    (
        SELECT AccountID, Amount
        FROM Inserted
        UNION ALL
        SELECT AccountID, $0 - Amount
        FROM Deleted
    ) AS InsDel
    GROUP BY
        InsDel.AccountID;

    UPDATE A
    SET Balance += D.Amount
    FROM #Delta AS D
    JOIN dbo.Accounts AS A WITH (SERIALIZABLE)
        ON A.AccountID = D.AccountID
    OPTION (RECOMPILE);
END;

teste

O código a seguir usa uma tabela de números para criar 100.000 contas com saldo zero:

INSERT dbo.Accounts
    (AccountID, Balance)
SELECT
    N.n, $0
FROM dbo.Numbers AS N
WHERE
    N.n BETWEEN 1 AND 100000;

O código de teste abaixo insere 10.000 transações aleatórias:

INSERT dbo.Transactions
    (AccountID, Amount)
SELECT 
    CONVERT(integer, RAND(CHECKSUM(NEWID())) * 100000 + 1),
    CONVERT(money, RAND(CHECKSUM(NEWID())) * 500 - 250)
FROM dbo.Numbers AS N
WHERE 
    N.n BETWEEN 1 AND 10000;

Usando a ferramenta SQLQueryStress , executei este teste 100 vezes em 32 threads com bom desempenho, sem deadlocks e resultados corretos. Eu ainda não recomendo isso como nada além de um exercício de aprendizado.