Uso de las tablas inserted y deleted

Se aplica a:SQL ServerAzure SQL DatabaseInstancia administrada de Azure SQLBase de datos SQL en Microsoft Fabric

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 se pueden modificar directamente los datos en las tablas ni realizar operaciones en lenguaje de definición de datos (DDL) sobre las tablas, 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 eliminada almacena copias de las filas afectadas en la tabla de disparadores antes de que fueran modificadas por una DELETE instrucción o UPDATE (la tabla de disparadores es la tabla sobre la que se ejecuta el disparador DML). Durante la ejecución de una DELETE instrucción o UPDATE , las filas afectadas se copian primero de la tabla de disparo y se transfieren a la tabla eliminada.

La tabla insertada almacena copias de las filas nuevas o modificadas después de una INSERT instrucción o UPDATE . Durante la ejecución de una INSERT instrucción o UPDATE , las filas nuevas o modificadas de la tabla de disparo se copian a la tabla insertada. 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 UPDATE afirmación, ocurre la siguiente secuencia de eventos:

1. La fila original se copia de la tabla desencadenadora a la tabla deleted.
2. La tabla de disparadores se actualiza con los nuevos valores de la UPDATE sentencia.
3. 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 referenciar la tabla eliminada al probar una INSERT o la tabla insertada al probar a DELETE no causa errores, estas tablas de prueba de disparador no contienen filas en estos casos.

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 obtener más información, vea 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
        INNER 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;
    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 sobre cuándo una INSERT sentencia o UPDATE que hace referencia a una tabla con un disparador INSTEAD OF debe proporcionar valores para columnas son las mismas que si la tabla no tuviera un disparador INSTEAD OF:

• No se pueden especificar los valores para las columnas calculadas ni para las que contienen el tipo de datos timestamp .

• No se pueden especificar valores para columnas con una IDENTITY propiedad, a menos que IDENTITY_INSERT esté ON para esa tabla. Cuando IDENTITY_INSERT está ACTIVADO, INSERT las sentencias deben aportar un valor.

• INSERT las sentencias deben proporcionar valores para todas las columnas NOT NULL que no tengan DEFAULT restricciones.

• Para cualquier columna excepto las de computed, identidad o de marca de tiempo , los valores son opcionales para cualquier columna que permita nulos, o para cualquier columna NO NULA que tenga una DEFAULT definición.

Cuando una INSERTinstrucción , UPDATE, o DELETE referencia a una vista que tiene un disparador INSTEAD OF, el Motor de base de datos llama al disparador en lugar de tomar acción directa contra 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 sobre el 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 combinarlas con otras tablas puede dar lugar a planes de consulta sub óptimos 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.