Uso de las tablas inserted y deleted

Artículo
05/30/2023

Se aplica a:SQL Server Azure SQL Database Azure SQL Managed Instance

En las instrucciones de desencadenadores DML se usan dos tablas especiales: la tabla inserted y la tabla deleted. SQL Server crea y administra automáticamente estas tablas. Puede utilizar estas tablas temporales residentes en memoria para probar los efectos de determinadas modificaciones de datos y para establecer condiciones para las acciones de los desencadenadores DML. No puede modificar directamente los datos de estas tablas ni realizar en ellas operaciones de lenguaje de definición de datos (DDL), como CREATE INDEX.

Descripción de las tablas inserted y deleted

En los desencadenadores DML, las tablas inserted y deleted se utilizan principalmente para realizar las siguientes tareas:

Ampliar la integridad referencial entre tablas.
Insertar o actualizar datos de tablas base subyacentes a una vista.
Comprobar errores y realizar acciones en función del error.
Conocer la diferencia entre el estado de una tabla antes y después de realizar una modificación en los datos, y actuar en función de dicha diferencia.

La tabla deleted almacena copias de las filas afectadas en la tabla desencadenadora antes de que se cambien debido a una instrucción DELETE o UPDATE (la tabla desencadenadora es aquella en la que se ejecuta el desencadenador DML). Durante la ejecución de una instrucción DELETE o UPDATE, las filas afectadas se copian primero de la tabla desencadenadora y se transfieren a la tabla deleted.

La tabla inserted almacena copias de las filas nuevas o modificadas después de una instrucción INSERT o UPDATE. Durante la ejecución de una instrucción INSERT o UPDATE, las filas nuevas o modificadas de la tabla desencadenadora se copian en la tabla inserted. Las filas de la tabla inserted son copias de las filas nuevas o actualizadas de la tabla desencadenadora.

Una transacción de actualización es similar a una operación de eliminación seguida de una operación de inserción. Durante la ejecución de una instrucción UPDATE, se produce la siguiente secuencia de eventos:

La fila original se copia de la tabla desencadenadora a la tabla deleted.
La tabla desencadenadora se actualiza con los nuevos valores de la instrucción UPDATE.
La fila actualizada de la tabla desencadenador se copia en la tabla inserted.

Esto le permite comparar el contenido de la fila anterior a la actualización (en la tabla deleted) con los nuevos valores de fila después de la actualización (en la tabla inserted).

Cuando establezca condiciones para el desencadenador, utilice las tablas inserted y deleted correspondientes a la acción que lo activó. Aunque no se produce ningún error al hacer referencia a la tabla deleted cuando se prueba una instrucción INSERT, o bien al hacer referencia a la tabla inserted cuando se prueba una instrucción DELETE, estas tablas de prueba del desencadenador no contendrán filas en estos casos.

Nota:

Si las acciones del desencadenador dependen del número de filas afectadas por una modificación de datos, utilice pruebas (como, por ejemplo, un examen de @@ROWCOUNT) para las modificaciones de datos que afecten a varias filas (instrucciones INSERT, DELETE o UPDATE basadas en una instrucción SELECT) y tome las medidas oportunas. Para más información, consulte Crear desencadenadores DML para administrar varias filas de datos.

SQL Server no permite referencias a las columnas text, ntext o image en las tablas insertadas y eliminadas por los desencadenadores AFTER. Sin embargo, estos tipos de datos se incluyen únicamente por motivos de compatibilidad con versiones anteriores. El método preferido para almacenar datos grandes es usar los tipos de datos varchar(max), nvarchar(max)y varbinary(max) . Tanto los desencadenadores AFTER como INSTEAD OF admiten los datos varchar(max), nvarchar(max)y varbinary(max) en las tablas insertadas y eliminadas. Para más información, consulte CREATE TRIGGER (Transact-SQL).

Ejemplo: Uso de la tabla inserted en un desencadenador para aplicar reglas de negocios

Debido a que las restricciones CHECK solo pueden hacer referencia a las columnas en las que se han definido las restricciones de columna o de tabla, cualquier restricción entre tablas, en este caso, reglas de negocios, debe definirse como desencadenadores.

En este ejemplo se crea un desencadenador DML. El desencadenador comprueba que la solvencia del proveedor es satisfactoria cuando se intenta insertar un nuevo pedido de compra en la tabla PurchaseOrderHeader . Para obtener la solvencia de crédito del proveedor correspondiente al pedido de compra recién insertado, la tabla Vendor debe hacer referencia a la tabla insertada y estar combinada con ella. Si la solvencia no es satisfactoria, se obtiene un mensaje y no se ejecuta la inserción.

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;  
GO  
  
-- This statement attempts to insert a row into the PurchaseOrderHeader table  
-- for a vendor that has a below average credit rating.  
-- The AFTER INSERT trigger is fired and the INSERT transaction is rolled back.  
  
INSERT INTO Purchasing.PurchaseOrderHeader (RevisionNumber, Status, EmployeeID,  
VendorID, ShipMethodID, OrderDate, ShipDate, SubTotal, TaxAmt, Freight)  
VALUES (  
2  
,3  
,261  
,1652  
,4  
,GETDATE()  
,GETDATE()  
,44594.55  
,3567.564  
,1114.8638 );  
GO

Uso de las tablas inserted y deleted en desencadenadores INSTEAD OF

Las tablas inserted y deleted pasadas a desencadenadores INSTEAD OF definidos en tablas siguen las mismas reglas que las tablas inserted y deleted pasadas a desencadenadores AFTER. El formato de las tablas inserted y deleted es el mismo que el de una tabla que tiene definido un desencadenador INSTEAD OF. Cada columna de las tablas inserted y deleted se asigna directamente a una columna de la tabla base.

Las siguientes reglas aplicables cuando una instrucción INSERT o UPDATE que hace referencia a una tabla con un desencadenador INSTEAD OF debe suministrar valores para las columnas, son las mismas que se utilizan cuando la tabla no tiene un desencadenador INSTEAD OF:

No se pueden especificar los valores para las columnas calculadas ni para las que contienen el tipo de datos timestamp .
Solo se pueden especificar valores para columnas con la propiedad IDENTITY si la opción IDENTITY_INSERT se ha establecido en ON para la tabla. Cuando el valor de IDENTITY_INSERT es ON, las instrucciones INSERT deben suministrar un valor.
Las instrucciones INSERT deben suministrar valores para todas las columnas NOT NULL que no tienen restricciones DEFAULT.
Los valores son opcionales para cualquier columna que admita valores NULL o para cualquier columna NOT NULL que tenga una definición DEFAULT, excepto para las columnas calculadas, de identidad o timestamp .

Cuando una instrucción INSERT, UPDATE o DELETE hace referencia a una vista que tiene un desencadenador INSTEAD OF, el motor de base de datos llama al desencadenador en lugar de realizar una acción directa en cualquier tabla. El desencadenador utiliza la información de las tablas inserted y deleted a fin de generar las instrucciones necesarias para implementar la acción solicitada en las tablas base, incluso si el formato de la información de las tablas inserted y deleted generadas para la vista es distinto del formato de los datos de las tablas base.

El formato de las tablas inserted y deleted pasado a un desencadenador INSTEAD OF definido en una vista coincide con la lista de selección de la instrucción SELECT definida para la vista. Por ejemplo:

USE AdventureWorks2022;  
GO  
CREATE VIEW dbo.EmployeeNames (BusinessEntityID, LName, FName)  
AS  
SELECT e.BusinessEntityID, p.LastName, p.FirstName  
FROM HumanResources.Employee AS e   
JOIN Person.Person AS p  
ON e.BusinessEntityID = p.BusinessEntityID;

El conjunto de resultados de esta vista tiene tres columnas: una columna de tipo int y dos columnas de tipo nvarchar . Las tablas insertadas y eliminadas a un desencadenador INSTEAD OF también tiene una columna int llamada BusinessEntityID, una columna nvarchar llamada LNamey una columna nvarchar llamada FName.

La lista de selección de una vista también puede contener expresiones que no se asignen directamente a una sola columna de tabla base. Es posible que algunas expresiones de las vistas, como las llamadas a constantes o funciones, no hagan referencia a ninguna columna y puedan omitirse. Las expresiones complejas pueden hacer referencia a varias columnas, aunque las tablas inserted y deleted solo tienen un valor para cada fila insertada. Esto mismo se aplica a las expresiones sencillas de una vista si hacen referencia a columnas calculadas que contienen expresiones complejas. Los desencadenadores INSTEAD OF de las vistas pueden tratar estos tipos de expresiones.

Consideraciones de rendimiento

Dado que las tablas insertadas y eliminadas son tablas virtuales residentes en RAM, propiedades como las estadísticas o los índices no están disponibles. Aunque se expone alguna información de cardinalidad en estas tablas, debe tener cuidado al considerar el número de filas que se almacenarán temporalmente allí. Insertar un gran número de filas en estas tablas y consultarlas o unirlas con otras tablas puede dar lugar a planes de consulta poco optimizados y ejecuciones de consultas lentas. Asegúrese de diseñar y probar cuidadosamente la aplicación para satisfacer sus necesidades de rendimiento de consultas.

Pasos siguientes

Para obtener más información, consulte la introducción a los desencadenadores DML.