Diseñar desencadenadores INSTEAD OF
La principal ventaja de los desencadenadores INSTEAD OF es que permiten a las vistas que no serían actualizables admitir actualizaciones. Las vistas que contengan varias tablas base deben utilizar un desencadenador INSTEAD OF para permitir inserciones, actualizaciones y eliminaciones que hagan referencia a datos de más de una tabla. Otra ventaja de los desencadenadores INSTEAD OF es que permiten codificar la lógica para rechazar partes de un lote y, al mismo tiempo, aceptar otras partes del mismo.
Un desencadenador INSTEAD OF puede realizar acciones como:
Omitir partes de un lote.
No procesar una parte de un lote y registrar las filas que presentan problemas.
Realizar una acción alternativa si se produce una condición de error.
Nota
No se pueden definir desencadenadores INSTEAD OF DELETE ni INSTEAD OF UPDATE en tablas que contengan una clave externa definida mediante una acción en cascada DELETE o UPDATE.
La codificación de esta lógica como parte de un desencadenador INSTEAD OF evita que todas las aplicaciones que obtienen acceso a los datos tengan que volver a implementar la lógica.
Ejemplo
En la siguiente secuencia de instrucciones Transact-SQL, un desencadenador INSTEAD OF actualiza dos tablas base desde una vista. Además, se muestran los siguientes enfoques de control de errores:
Las inserciones duplicadas de la tabla Person se omiten y la información de la inserción se registra en la tabla PersonDuplicates.
Las inserciones de duplicados en EmployeeTable se convierten en una instrucción UPDATE que recupera la información actual de la tabla EmployeeTable sin generar una infracción de clave duplicada.
Las instrucciones Transact-SQL crean dos tablas base, una vista, una tabla para registrar errores y el desencadenador INSTEAD OF en la vista. Las siguientes tablas separan la información personal de la empresarial y constituyen las tablas base de la vista:
CREATE TABLE Person
(
SSN char(11) PRIMARY KEY,
Name nvarchar(100),
Address nvarchar(100),
Birthdate datetime
)
CREATE TABLE EmployeeTable
(
EmployeeID int PRIMARY KEY,
SSN char(11) UNIQUE,
Department nvarchar(10),
Salary money,
CONSTRAINT FKEmpPer FOREIGN KEY (SSN)
REFERENCES Person (SSN)
)
Esta vista presenta los datos importantes de las dos tablas acerca de una persona:
CREATE VIEW Employee AS
SELECT P.SSN as SSN, Name, Address,
Birthdate, EmployeeID, Department, Salary
FROM Person P, EmployeeTable E
WHERE P.SSN = E.SSN
Puede registrar intentos de insertar filas con números de seguridad social duplicados. La tabla PersonDuplicates registra los valores insertados, el nombre del usuario que intentó realizar la inserción y la hora de la inserción.
CREATE TABLE PersonDuplicates
(
SSN char(11),
Name nvarchar(100),
Address nvarchar(100),
Birthdate datetime,
InsertSNAME nchar(100),
WhenInserted datetime
)
El desencadenador INSTEAD OF inserta filas en varias tablas base desde una única vista. Los intentos de insertar filas con números de seguridad social duplicados se registran en la tabla PersonDuplicates. Las filas duplicadas de EmployeeTable se cambian por instrucciones de actualización.
CREATE TRIGGER IO_Trig_INS_Employee ON Employee
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
-- Check for duplicate Person. If there is no duplicate, do an insert.
IF (NOT EXISTS (SELECT P.SSN
FROM Person P, inserted I
WHERE P.SSN = I.SSN))
INSERT INTO Person
SELECT SSN,Name,Address,Birthdate
FROM inserted
ELSE
-- Log an attempt to insert duplicate Person row in PersonDuplicates table.
INSERT INTO PersonDuplicates
SELECT SSN,Name,Address,Birthdate,SUSER_SNAME(),GETDATE()
FROM inserted
-- Check for duplicate Employee. If no there is duplicate, do an INSERT.
IF (NOT EXISTS (SELECT E.SSN
FROM EmployeeTable E, inserted
WHERE E.SSN = inserted.SSN))
INSERT INTO EmployeeTable
SELECT EmployeeID,SSN, Department, Salary
FROM inserted
ELSE
--If there is a duplicate, change to UPDATE so that there will not
--be a duplicate key violation error.
UPDATE EmployeeTable
SET EmployeeID = I.EmployeeID,
Department = I.Department,
Salary = I.Salary
FROM EmployeeTable E, inserted I
WHERE E.SSN = I.SSN
END