Conception de déclencheurs INSTEAD OF

Le principal avantage des déclencheurs INSTEAD OF est qu'ils autorisent des vues qui autrement ne pourraient pas être mises à jour en vue de la prise en charge des mises à jour. Une vue basée sur plusieurs tables de base doit posséder un déclencheur INSTEAD OF pour prendre en charge les opérations d'insertion, de mise à jour et de suppression référençant des données dans plusieurs tables. Un autre avantage des déclencheurs INSTEAD OF est qu'ils permettent de programmer une logique capable de rejeter certaines parties d'un lot d'instructions et d'en mener d'autres à terme.

Un déclencheur INSTEAD OF peut effectuer des opérations comme :

• ignorer des parties d'un lot d'instructions ;
• ne pas traiter une partie d'un lot et consigner dans le journal les lignes problématiques ;
• appliquer une action de substitution si une erreur se produit.

  Remarque :
  Les déclencheurs INSTEAD OF DELETE et INSTEAD OF UPDATE ne peuvent pas être définis sur une table qui a une clé étrangère définie avec une action DELETE ou UPDATE en cascade.

Grâce à la programmation de cette logique dans le cadre d'un déclencheur INSTEAD OF, celle-ci ne doit pas être implémentée à nouveau par toutes les applications qui accèdent aux données.

Exemple

Dans la séquence suivante d'instructions Transact-SQL, un déclencheur INSTEAD OF met à jour deux tables de base à partir d'une vue. De plus, les approches de traitement des erreurs suivantes sont indiquées :

• Les insertions dupliquées dans la table Person sont ignorées et les informations issues des insertions sont consignées dans la table PersonDuplicates.
• Les insertions de doublons dans la table EmployeeTable sont converties en instruction UPDATE qui extrait les informations actuelles et les place dans la table EmployeeTable sans générer de violation de la clé dupliquée.

Les instructions Transact-SQL créent deux tables de base, une vue, une table d'enregistrement des erreurs et le déclencheur INSTEAD OF sur la vue. Les tables suivantes séparent les données personnelles et d'entreprise et elles représentent les tables de base pour la vue.

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)
   )

La vue suivante indique toutes les données pertinentes relatives à une personne et issues des deux tables.

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

Vous pouvez enregistrer les tentatives d'insertion de lignes présentant des numéros de sécurité sociale dupliqués. La table PersonDuplicates consigne les valeurs insérées, le nom de l'utilisateur à l'origine de la tentative d'insertion et l'heure de l'insertion.

CREATE TABLE PersonDuplicates
   (
    SSN           char(11),
    Name          nvarchar(100),
    Address       nvarchar(100),
    Birthdate     datetime,
    InsertSNAME   nchar(100),
    WhenInserted  datetime
   )

Le déclencheur INSTEAD OF insère des lignes dans plusieurs tables de base à partir d'une simple vue. Les tentatives d'insertion de lignes présentant des numéros de sécurité sociale dupliqués sont enregistrées dans la table PersonDuplicates. Les lignes dupliquées de la table EmployeeTable sont converties en instructions de mise à jour.

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

Voir aussi

Concepts

Création de déclencheurs DML

Aide et Informations

Assistance sur SQL Server 2005