Crear desencadenadores DML para administrar varias filas de datos

Al escribir el código para un desencadenador DML, tenga en cuenta que la instrucción que hace que se active el desencadenador puede ser una sola instrucción que afecte a varias filas de datos, en lugar de una sola fila. Este comportamiento es común para los desencadenadores UPDATE y DELETE, ya que estas instrucciones afectan con frecuencia a varias filas. El comportamiento es menos común para los desencadenadores INSERT porque la instrucción INSERT básica agrega solo una sola fila. Sin embargo, dado que un desencadenador INSERT se puede desencadenar mediante una instrucción SELECT INSERT INTO (table_name), la inserción de muchas filas puede provocar una sola invocación de desencadenador.

Las consideraciones de múltiples filas son especialmente importantes cuando la función de un desencadenador DML es recalcular automáticamente los valores de resumen de una tabla y almacenar los resultados en otra para las acumulaciones continuas.

Ejemplos

Los desencadenadores DML de los ejemplos siguientes están diseñados para almacenar un total en ejecución de una columna en otra tabla de la base de datos de ejemplo AdventureWorks2012 .

A. Guardar un total acumulado para una inserción de una sola fila

La primera versión del desencadenador DML funciona bien para una inserción de una sola fila cuando se carga una fila de datos en la PurchaseOrderDetail tabla. Una instrucción INSERT desencadena el desencadenador DML y la nueva fila se carga en la tabla insertada durante la ejecución del desencadenador. La UPDATE instrucción lee el valor de la columna LineTotal de la fila y agrega ese valor al valor existente en la columna SubTotal en la tabla PurchaseOrderHeader. La WHERE cláusula garantiza que la fila actualizada de la PurchaseOrderDetail tabla coincide con la PurchaseOrderID de la fila de la tabla insertada .

-- 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. Guardar un total acumulado para una inserción de múltiples filas o una sola fila

Para una inserción multirow, es posible que el desencadenador DML del ejemplo A no funcione correctamente; la expresión a la derecha de una expresión de asignación en una instrucción UPDATE (SubTotal + LineTotal) solo puede ser un valor único, no una lista de valores. Por lo tanto, el efecto del desencadenador es recuperar un valor de cualquier fila única de la tabla insertada y agregar ese valor al valor existente SubTotal de la PurchaseOrderHeader tabla para un valor específico PurchaseOrderID . Es posible que esta operación no tenga el efecto esperado si se produjo un único PurchaseOrderID valor más de una vez en la tabla insertada .

Para actualizar correctamente la PurchaseOrderHeader tabla, el desencadenador debe permitir la posibilidad de que haya varias filas en la tabla insertada . Para ello, use la SUM función que calcula el total LineTotal de un grupo de filas de la tabla insertada para cada PurchaseOrderID. La función SUM está incluida en una subconsulta correlacionada (la instrucción SELECT entre paréntesis). Esta subconsulta devuelve un valor único para cada PurchaseOrderID uno de la tabla insertada que coincide o se correlaciona con un elemento PurchaseOrderID de la PurchaseOrderHeader tabla.

-- 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);  

Este disparador también funciona correctamente en la inserción de una única fila; la suma de la columna LineTotal value es igual a la de una sola fila. Sin embargo, con este desencadenador la subconsulta correlacionada y el IN operador que se usa en la WHERE cláusula requieren procesamiento adicional de SQL Server. Esto no es necesario para una inserción de fila única.

C. Almacenar un total acumulado basado en el tipo de inserción

Puede cambiar el desencadenador para usar el método óptimo para el número de filas. Por ejemplo, la función @@ROWCOUNT se puede usar en la lógica del activador para distinguir entre una sola inserción y una inserción multifila.

-- 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;  

Véase también

Desencadenadores DML