Compartir a través de


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

Vea también

Conceptos