Criar gatilhos DML para lidar com várias linhas de dados

Aplica-se a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBase de dados SQL no Microsoft Fabric

Ao escrever o código para um gatilho DML, considere que a instrução que faz com que o gatilho seja acionado pode ser uma única instrução que afeta várias linhas de dados, em vez de uma única linha. Esse comportamento é comum para gatilhos UPDATE e DELETE porque essas instruções freqüentemente afetam várias linhas. O comportamento é menos comum para gatilhos INSERT porque a instrução INSERT básica adiciona apenas uma única linha. No entanto, como um gatilho INSERT pode ser acionado por uma instrução INSERT INTO (table_name) SELECT, a inserção de muitas linhas pode causar uma única chamada de gatilho.

As considerações de múltiplas linhas são especialmente importantes quando a função de um gatilho DML é recalcular automaticamente os valores agregados de uma tabela e armazenar os resultados em outra para contagens contínuas.

Observação

Não recomendamos o uso de cursores em gatilhos porque eles podem reduzir o desempenho. Para criar um gatilho que afete várias linhas, use a lógica baseada em conjunto de linhas em vez de cursores.

Exemplos

Os gatilhos DML nos exemplos seguintes destinam-se a armazenar o total acumulado de uma coluna noutra tabela do banco de dados de exemplo AdventureWorks2025.

Um. Armazenando um total em execução para uma inserção de linha única

A primeira versão do gatilho DML funciona bem para uma inserção de uma única linha quando uma linha de dados é carregada na tabela PurchaseOrderDetail. Uma instrução INSERT ativa o gatilho DML, e a nova linha é inserida na tabela inserido durante a execução do gatilho. A instrução UPDATE lê o valor da coluna LineTotal para a linha e adiciona esse valor ao valor existente na coluna SubTotal na tabela PurchaseOrderHeader. A cláusula WHERE garante que a linha atualizada na tabela PurchaseOrderDetail corresponda à PurchaseOrderID da linha inserida na tabela .

-- Trigger is valid for single-row inserts.  
USE AdventureWorks2022;  
GO  
CREATE TRIGGER NewPODetail  
ON Purchasing.PurchaseOrderDetail  
AFTER INSERT AS  
   UPDATE PurchaseOrderHeader  
   SET SubTotal = SubTotal + LineTotal  
   FROM inserted  
   WHERE PurchaseOrderHeader.PurchaseOrderID = inserted.PurchaseOrderID ;  

B. Armazenando um total acumulado para uma inserção de várias linhas ou de uma única linha

Para uma inserção de várias linhas, o gatilho DML no exemplo A pode não funcionar corretamente; a expressão à direita de uma expressão de atribuição em uma instrução UPDATE (SubTotal + LineTotal) pode ser apenas um único valor, não uma lista de valores. Portanto, o efeito do gatilho é recuperar um valor de qualquer linha na tabela inserida e adicionar esse valor ao valor de SubTotal existente na tabela PurchaseOrderHeader para um valor PurchaseOrderID específico. Esta operação pode não ter o efeito esperado se um único valor de PurchaseOrderID ocorreu mais de uma vez no inserido tabela.

Para atualizar corretamente a tabela PurchaseOrderHeader, o gatilho deve permitir a possibilidade de várias linhas na tabela inserida . Você pode fazer isso usando a função SUM que calcula o LineTotal total para um grupo de linhas na tabela inserida para cada PurchaseOrderID. A função SUM é incluída em uma subconsulta correlacionada (a instrução SELECT entre parênteses). Essa subconsulta retorna um único valor para cada PurchaseOrderID na tabela inserida em que corresponde ou está correlacionada com um PurchaseOrderID na tabela PurchaseOrderHeader.

-- Trigger is valid for multirow and single-row inserts.  
USE AdventureWorks2022;  
GO  
CREATE TRIGGER NewPODetail2  
ON Purchasing.PurchaseOrderDetail  
AFTER INSERT AS  
   UPDATE Purchasing.PurchaseOrderHeader  
   SET SubTotal = SubTotal +   
      (SELECT SUM(LineTotal)  
      FROM inserted  
      WHERE PurchaseOrderHeader.PurchaseOrderID  
       = inserted.PurchaseOrderID)  
   WHERE PurchaseOrderHeader.PurchaseOrderID IN  
      (SELECT PurchaseOrderID FROM inserted);  

Este gatilho também funciona corretamente em uma inserção de uma única linha; a soma do valor da coluna LineTotal é a soma de uma única linha. No entanto, com esse disparador, a subconsulta correlacionada e o operador IN usado na cláusula WHERE exigem processamento adicional do SQL Server. Isso é desnecessário para uma inserção de linha única.

C. Armazenando um total acumulado continuamente com base no tipo de inserção

Você pode alterar o gatilho para usar o método ideal para o número de linhas. Por exemplo, a função @@ROWCOUNT pode ser usada na lógica do gatilho para distinguir entre uma inserção única e uma inserção de várias linhas.

-- Trigger valid for multirow and single row inserts  
-- and optimal for single row inserts.  
USE AdventureWorks2022;  
GO  
CREATE TRIGGER NewPODetail3  
ON Purchasing.PurchaseOrderDetail  
FOR INSERT AS  
IF @@ROWCOUNT = 1  
BEGIN  
   UPDATE Purchasing.PurchaseOrderHeader  
   SET SubTotal = SubTotal + LineTotal  
   FROM inserted  
   WHERE PurchaseOrderHeader.PurchaseOrderID = inserted.PurchaseOrderID  
END  
ELSE  
BEGIN  
      UPDATE Purchasing.PurchaseOrderHeader  
   SET SubTotal = SubTotal +   
      (SELECT SUM(LineTotal)  
      FROM inserted  
      WHERE PurchaseOrderHeader.PurchaseOrderID  
       = inserted.PurchaseOrderID)  
   WHERE PurchaseOrderHeader.PurchaseOrderID IN  
      (SELECT PurchaseOrderID FROM inserted)  
END;  

Ver também

DML aciona