Usar las tablas insertadas y eliminadas

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 ambas 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.

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 por las instrucciones DELETE y UPDATE. Durante la ejecución de una instrucción DELETE o UPDATE, las filas se eliminan de la tabla del desencadenador y se transfieren a la tabla deleted. La tabla deleted y la tabla del desencadenador no suelen tener filas en común.

La tabla inserted almacena copias de las filas afectadas durante las instrucciones INSERT y UPDATE. Durante una transacción de inserción o actualización, se agregan nuevas filas a la tabla inserted y a la tabla del desencadenador. Las filas de la tabla inserted son copias de las nuevas filas de la tabla del desencadenador.

Una transacción de actualización es similar a una operación de eliminación seguida de una operación de inserción; primero, se copian las filas antiguas en la tabla deleted y luego se copian las filas nuevas en la tabla del desencadenador y 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.

SQL Server 2014 no permite textreferencias de columna , ntexto image en las tablas insertadas y eliminadas para desencadenadores AFTER. Sin embargo, estos tipos de datos se incluyen únicamente por motivos de compatibilidad con versiones anteriores. El almacenamiento preferido para datos de gran tamaño es el uso de 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 del uso de la tabla inserted en un desencadenador para exigir 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. Tenga en cuenta que en este ejemplo no se permiten las modificaciones de datos en varias filas. Para más información, consulte Crear desencadenadores DML para administrar varias filas de datos.

USE AdventureWorks2012;
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 EXISTS (SELECT *
           FROM Purchasing.PurchaseOrderHeader p 
           JOIN inserted AS i 
           ON p.PurchaseOrderID = i.PurchaseOrderID 
           JOIN Purchasing.Vendor AS v 
           ON v.BusinessEntityID = p.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

Utilizar 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 AdventureWorks2012;  
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 LName y 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.