Usar las tablas inserted y deleted
Las instrucciones de desencadenadores DML utilizan dos tablas especiales: la tabla deleted y la tabla inserted. 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 del desencadenador 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.
[!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.
SQL Server 2008 no permite referencias a columnas text, ntext o image en las tablas inserted y deleted de desencadenadores AFTER.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 datos varchar(max), nvarchar(max) y varbinary(max) en las tablas inserted y deleted. Para obtener más información, vea CREATE TRIGGER (Transact-SQL).
Ejemplo del uso de la tabla insertada en un desencadenador para exigir reglas de negocios
Debido a que las restricciones CHECK sólo pueden hacer referencia a las columnas en 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 inserted y estar combinada con ella. Si la solvencia no es satisfactoria, se obtiene un mensaje y no se ejecuta la inserción.
[!NOTA]
Para ver ejemplos de desencadenadores DML AFTER que actualizan varias filas, vea Consideraciones acerca de operaciones con varias filas para desencadenadores DML.
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.VendorID = p.VendorID
WHERE v.CreditRating = 5
)
BEGIN
RAISERROR ('This vendor''s credit rating is too low to accept new
purchase orders.', 16, 1);
ROLLBACK TRANSACTION;
RETURN
END;
-- 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.
Sólo 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, Database Engine (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:
CREATE VIEW EmployeeNames (EmployeeID, LName, FName)
AS
SELECT e.EmployeeID, c.LastName, c.FirstName
FROM AdventureWorks.HumanResources.Employee e
JOIN AdventureWorks.Person.Contact c
ON e.ContactID = c.ContactID
El conjunto de resultados de esta vista tiene tres columnas: una columna int y dos columnas nvarchar. Las tablas inserted y deleted pasadas a un desencadenador INSTEAD OF definido en la vista también tienen una columna int denominada EmployeeID, una columna nvarchar denominada LName y una columna nvarchar denominada 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 sólo 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. Para obtener más información, vea Expresiones y columnas calculadas de los desencadenadores INSTEAD OF.