Crie gatilhos DML para tratar várias linhas de dados

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 frequentemente 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 invocação de gatilho.

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

Exemplos

Os gatilhos DML nos exemplos a seguir são projetados para armazenar um total acumulado de uma coluna em outra tabela do banco de dados de exemplo AdventureWorks2012.

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

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 aciona o gatilho DML e a nova linha é carregada na tabela inserida durante a execução do gatilho. A UPDATE instrução lê o valor da LineTotal coluna da linha e adiciona esse valor ao valor existente na SubTotal coluna na PurchaseOrderHeader tabela. A WHERE cláusula garante que a linha atualizada na PurchaseOrderDetail tabela corresponda à PurchaseOrderID linha na tabela inserida .

-- Trigger is valid for single-row inserts.  
USE AdventureWorks2012;  
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 em execução para uma inserção de várias linhas ou de linha única

Para uma inserção de várias linhas, o gatilho DML no exemplo A pode não operar 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 única na tabela inserida e adicionar esse valor ao valor existente SubTotal na PurchaseOrderHeader tabela para um valor específico PurchaseOrderID . Essa operação pode não ter o efeito esperado se um único PurchaseOrderID valor ocorreu mais de uma vez na tabela inserida .

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

-- Trigger is valid for multirow and single-row inserts.  
USE AdventureWorks2012;  
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);  

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

C. Armazenando um total em execução 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 @@ROWCOUNT função pode ser usada na lógica do gatilho para distinguir entre uma única e uma inserção de várias linhas.

-- Trigger valid for multirow and single row inserts  
-- and optimal for single row inserts.  
USE AdventureWorks2012;  
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;  

Consulte Também

Gatilhos DML