Utilice las tablas insertadas y eliminadas

Las instrucciones de desencadenador DML usan dos tablas especiales: la tabla eliminada y las tablas insertadas. SQL Server crea y administra automáticamente estas tablas. Puede usar 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 se pueden modificar directamente los datos de las tablas ni realizar operaciones de lenguaje de definición de datos (DDL) en las tablas, como CREATE INDEX.

En los desencadenadores DML, las tablas insertadas y eliminadas se usan principalmente para realizar lo siguiente:

• Extender la integridad referencial entre tablas.

• Insertar o actualizar datos en tablas base subyacentes a una vista.

• Pruebe si hay errores y tome medidas en función del error.

• Busque la diferencia entre el estado de una tabla antes y después de una modificación de datos y realice acciones basadas en esa diferencia.

La tabla eliminada almacena copias de las filas afectadas durante las instrucciones DELETE y UPDATE. Durante la ejecución de una instrucción DELETE o UPDATE, las filas se eliminan de la tabla de desencadenador y se transfieren a la tabla eliminada. La tabla eliminada y la tabla de activación normalmente no tienen filas en común.

La tabla insertada 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 de inserciones y a la tabla de desencadenadores. Las filas de la tabla de inserción son copias de las nuevas filas de la tabla trigger.

Una transacción de actualización es similar a una operación de eliminación seguida de una operación de inserción; las filas antiguas se copian primero en la tabla eliminada y, a continuación, las nuevas filas se copian en la tabla desencadenador y en la tabla insertada.

Al establecer las condiciones del activador, use las tablas insertadas y eliminadas de forma adecuada para la acción que activó el disparador. Aunque al hacer referencia a la tabla eliminada al probar una tabla INSERT o insertada al probar una DELETE no se producen errores, estas tablas de prueba de desencadenador no contienen ninguna fila en estos casos.

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

Ejemplo de uso de la tabla insertada en un desencadenador para aplicar reglas de negocios

Dado que las restricciones CHECK solo pueden hacer referencia a las columnas en las que se define la restricción de nivel de columna o de tabla, las restricciones entre tablas (en este caso, las reglas de negocio) deben definirse como desencadenadores.

En el ejemplo siguiente se crea un desencadenador DML. Este desencadenador comprueba si la clasificación de crédito del proveedor es buena cuando se intenta insertar un nuevo pedido de compra en la PurchaseOrderHeader tabla. Para obtener la clasificación de crédito del proveedor correspondiente al pedido de compra que se acaba de insertar, se debe hacer referencia a la Vendor tabla y combinarla con la tabla insertada. Si la clasificación de crédito es demasiado baja, se muestra un mensaje y la inserción no se ejecuta. Tenga en cuenta que este ejemplo no permite modificaciones de datos multirow. Para obtener más información, vea Crear desencadenadores DML para controlar 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

Uso de las tablas insertadas y eliminadas en LOS desencadenadores INSTEAD OF

Las tablas insertadas y eliminadas que se pasan a los desencadenadores INSTEAD OF definidos en las tablas siguen las mismas reglas que las tablas insertadas y eliminadas que se pasan a los desencadenadores AFTER. El formato de las tablas insertadas y eliminadas es el mismo que el formato de la tabla en la que se define el desencadenador INSTEAD OF. Cada columna de las tablas insertadas y eliminadas se asigna directamente a una columna de la tabla base.

Las siguientes reglas relacionadas con cuando una instrucción INSERT o UPDATE que hace referencia a una tabla con un desencadenador INSTEAD OF deben proporcionar valores para las columnas son las mismas que si la tabla no tuviera un desencadenador INSTEAD OF:

• No se pueden especificar valores para columnas calculadas ni columnas con un timestamp tipo de datos.

• Los valores no se pueden especificar para las columnas con una propiedad IDENTITY, a menos que IDENTITY_INSERT esté activado para esa tabla. Cuando IDENTITY_INSERT es ON, las instrucciones INSERT deben proporcionar un valor.

• Las instrucciones INSERT deben proporcionar valores para todas las columnas NOT NULL que no tengan valores predeterminados.

• Para cualquier columna excepto las calculadas, de identidad o timestamp, los valores son opcionales para cualquier columna que permita nulos o para cualquier columna NOT NULL que tenga una definición por defecto.

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 cualquier acción directa en cualquier tabla. El desencadenador debe usar la información presentada en las tablas insertadas y eliminadas para compilar las instrucciones necesarias para implementar la acción solicitada en las tablas base, incluso cuando el formato de la información de las tablas insertadas y eliminadas creadas para la vista es diferente del formato de los datos de las tablas base.

El formato de las tablas insertadas y eliminadas que se pasan 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 int columna y dos nvarchar columnas. Las tablas insertadas y eliminadas que se pasan a un desencadenador INSTEAD OF definido en la vista también tienen una columna denominada int, una columna denominada BusinessEntityID, una columna denominada LName, y una columna denominada FName.

La lista de selección de una vista también puede contener expresiones que no se asignan directamente a una sola columna de tabla base. Algunas expresiones de vista, como una invocación de constante o función, pueden no hacer referencia a ninguna columna y se pueden omitir. Las expresiones complejas pueden hacer referencia a varias columnas, pero las tablas insertadas y eliminadas solo tienen un valor para cada fila insertada. Los mismos problemas se aplican a expresiones simples en una vista si hacen referencia a una columna calculada que tiene una expresión compleja. Un desencadenador INSTEAD OF en la vista debe gestionar estos tipos de expresiones.