Início / dba / Perguntas / 335904
Accepted
user23278799
Asked: 2024-02-16 11:13:46 +0800 CST

Como impor corretamente a integridade referencial em um gatilho de atualização/inserção?

  • 772

Banco de dados: SQL Server, 2017.

Preciso impor certas regras de negócios em um gatilho. Agora, antes de me dizer para usar uma chave estrangeira, saiba que as regras são muito complicadas para serem aplicadas por chaves estrangeiras. Com isso resolvido, deixe-me ir ao cerne da minha pergunta:

Esta é a maneira correta de reverter/cancelar a inserção/atualização em um gatilho se uma regra de negócios for violada:

CREATE TRIGGER [dbo].[MyTestTrigger]
   ON  [dbo].[MyTestTable]
   AFTER INSERT, UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    IF TRIGGER_NESTLEVEL(OBJECT_ID('[dbo].[MyTestTrigger]')) > 1
    BEGIN
        RETURN;
    END

    if (condition_is_true) begin
         ;throw 51000, 'Bad data Message.',1;  
    end
end

Sei que minha pergunta parece simples, mas me deparei com esse " tratado " sobre tratamento de erros no SQL Server. Depois de folheá-lo, tive a impressão de que o tratamento de erros é extremamente complicado. Uma coisa que me deixa confuso é: preciso chamar explicitamente a rollbackinstrução antes/depois da throwinstrução? Ou ele throwpróprio reverterá a atualização/inserção. Dos meus testes, o throwpróprio reverte a atualização/inserção. No entanto, na própria documentação da Microsoft sobre gatilhos, a rollbackdeclaração está incluída:

USE AdventureWorks2022;
GO
IF OBJECT_ID ('Purchasing.LowCredit','TR') IS NOT NULL
   DROP TRIGGER Purchasing.LowCredit;
GO
-- This trigger prevents a row from being inserted in the Purchasing.PurchaseOrderHeader table
-- when the credit rating of the specified vendor is set to 5 (below average).  
  
CREATE TRIGGER Purchasing.LowCredit ON Purchasing.PurchaseOrderHeader  
AFTER INSERT  
AS  
IF (ROWCOUNT_BIG() = 0)
RETURN;
IF EXISTS (SELECT 1  
           FROM inserted AS i   
           JOIN Purchasing.Vendor AS v   
           ON v.BusinessEntityID = i.VendorID  
           WHERE v.CreditRating = 5  
          )  
BEGIN  
RAISERROR ('A vendor''s credit rating is too low to accept new  
purchase orders.', 16, 1);  
ROLLBACK TRANSACTION;  
RETURN   
END;

A inclusão de é rollbackrealmente necessária?

sql-server

1 respostas

  1. Best Answer

    Não, absolutamente não.

    Em primeiro lugar, THROWsempre aborta o lote de qualquer maneira, para que a próxima instrução nunca seja executada, a menos que você tenha um arquivo BEGIN CATCH.

    E XACT_ABORTestá sempre ONem gatilhos, então uma reversão acontece de qualquer maneira.

    Mesmo se você usar RAISERRORwhich não necessariamente aborta, se você ROLLBACKusar um gatilho, receberá um erro espúrio:

    Msg 3609 Level 16 State 1 Line 1
The transaction ended in the trigger. The batch has been aborted.

    Alguns clientes não lidam com isso corretamente, então você acaba vendo apenas esse erro e não o erro real. Então é só usar THROWsozinho.

    Embora o “tratado” de Erland sobre tratamento de erros seja excelente, ele é amplamente mal compreendido. Trata-se de tratamento de erros , ou seja, lidar com um erro, e não lançar um.

    Na maioria dos casos, você não deseja lidar com o erro no SQL. Você quer que o cliente cuide disso e, para isso, não precisa de nenhum código complexo, apenas THROWe XACT_ABORT ON.

    Even BEGIN CATCHé ruim: não detecta todos os erros e não consegue lidar com vários erros juntos, conforme mencionado. Se você precisar de registro, use XEvents e não algum registrador próprio.

    ;é um terminador de instrução, não um iniciador. Encerre todas as suas declarações ou seja rescindido.

    • 1

relate perguntas