Fonction de mise à jour en TSQL déclencheur

Déclencheurs sont difficiles et vous avez besoin de penser en vrac quand vous êtes en train de créer un. Un déclencheur est activé une fois pour chaque instruction de mise à JOUR. Si l'instruction de mise à JOUR les mises à jour plusieurs lignes, le déclencheur ne sera qu'un feu qu'une seule fois. La fonction UPDATE() renvoie la valeur true pour une colonne lorsque cette colonne est inclus dans la mise à JOUR de la déclaration. Cette fonction permet d'améliorer l'efficacité de la déclenche en vous permettant de contourner la logique SQL lorsque cette colonne n'est même pas inclus dans la mise à jour de la déclaration. Elle ne vous dit pas si la valeur a changé pour une colonne dans une ligne donnée.

Voici un exemple de table...

CREATE TABLE tblSample
(
    SampleID INT PRIMARY KEY,
    SampleName VARCHAR(10),
    SampleNameLastChangedDateTime DATETIME,
    Parent_SampleID INT
)

Si l'instruction SQL suivante a été utilisée à l'encontre de ce tableau:

UPDATE tblSample SET SampleName = 'hello'

..et un APRÈS, d'INSERTION, de mise à JOUR déclencheur a été, en effet, cette instruction SQL serait toujours évaluer la fonction de mise à JOUR comme suit...

IF UPDATE(SampleName) --aways evaluates to TRUE
IF UPDATE(SampleID)  --aways evaluates to FALSE
IF UPDATE(Parent_SampleID) --aways evaluates to FALSE

Noter que la mise à JOUR(SampleName) serait toujours vrai pour cette instruction SQL, indépendamment de ce que l'SampleName valeurs étaient avant. Elle renvoie true car l'instruction de mise à JOUR inclut la colonne SampleName dans la section JEU de cette clause, et ne repose pas sur ce que les valeurs sont avant ou après. La fonction UPDATE() ne sera pas de déterminer si les valeurs ont changé. Si vous souhaitez effectuer des actions en fonction de si les valeurs sont modifiées, vous allez avoir besoin d'utiliser SQL et de comparer les inserted et deleted lignes.

Ici est une approche pour garder une colonne dernière mise à jour de synchronisation:

--/*
IF OBJECT_ID('dbo.tgr_tblSample_InsertUpdate', 'TR') IS NOT NULL 
  DROP TRIGGER dbo.tgr_tblSample_InsertUpdate
GO
--*/

CREATE TRIGGER dbo.tgr_tblSample_InsertUpdate ON dbo.tblSample
  AFTER INSERT, UPDATE 
AS
BEGIN --Trigger

  IF UPDATE(SampleName)  
    BEGIN
      UPDATE tblSample SET
      SampleNameLastChangedDateTime = CURRENT_TIMESTAMP
      WHERE
        SampleID IN (SELECT Inserted.SampleID 
               FROM Inserted LEFT JOIN Deleted ON Inserted.SampleID = Deleted.SampleID
               WHERE COALESCE(Inserted.SampleName, '') <> COALESCE(Deleted.SampleName, ''))
    END

END --Trigger

La logique pour déterminer si la ligne a été mis à jour est dans la clause where ci-dessus. C'est le véritable vérifiez que vous devez faire. Ma logique est à l'aide de FUSIONNER pour gérer les valeurs NULL et INSERTS.

...
WHERE
  SampleID IN (SELECT Inserted.SampleID 
               FROM Inserted LEFT JOIN Deleted ON Inserted.SampleID = Deleted.SampleID
               WHERE COALESCE(Inserted.SampleName, '') <> COALESCE(Deleted.SampleName, ''))

Noter que SI la mise à JOUR() vérification est utilisée pour aider à améliorer l'efficacité de la gâchette lorsque le SampleName colonne n'est PAS mis à jour. Si une instruction SQL de mise à jour de la Parent_SampleID colonne par exemple que SI la mise à JOUR(SampleName) vérifier permettrait pas de côté autour de la logique plus complexe dans la mesure où SI une déclaration n'a pas besoin de courir. Envisager l'utilisation de mise à JOUR() lorsque cela est approprié, mais pas pour les mauvaises raisons.

Aussi se rendre compte que, en fonction de votre architecture, la fonction de mise à JOUR peut avoir aucune utilité pour vous. Si votre code architecture utilise un intermédiaire qui met toujours à jour toutes les colonnes d'une ligne d'un tableau avec les valeurs de l'entreprise d'objet lorsque l'objet est enregistré, la fonction UPDATE() dans un déclencheur devient inutile. Dans ce cas, votre code est probablement toujours une mise à jour de toutes les colonnes avec chaque mise à JOUR de déclaration délivré à partir du niveau intermédiaire. Cela étant le cas, la mise à JOUR(columnname) fonction serait toujours vraies (true) lorsque les objets sont sauvés parce que tous les noms de colonnes sont toujours inclus dans la mise à jour des déclarations. Dans ce cas, il ne serait pas utile d'utiliser UPDATE() dans le déclenchement et le serait une charge supplémentaire en ce que le déclencheur pour une majorité de l'époque.

Voici quelques SQL de jouer avec le déclenchement ci-dessus:

INSERT INTO tblSample
(
  SampleID,
  SampleName
)
SELECT 1, 'One'
UNION SELECT 2, 'Two'
UNION SELECT 3, 'Three'

GO
SELECT SampleID, SampleName, SampleNameLastChangedDateTime FROM tblSample

/*
SampleID  SampleName SampleNameLastChangedDateTime
----------- ---------- -----------------------------
1       One    2010-10-27 14:52:42.567
2       Two    2010-10-27 14:52:42.567
3       Three  2010-10-27 14:52:42.567
*/

GO

INSERT INTO tblSample
(
  SampleID,
  SampleName
)
SELECT 4, 'Foo'
UNION SELECT 5, 'Five'

GO
SELECT SampleID, SampleName, SampleNameLastChangedDateTime FROM tblSample
/*
SampleID  SampleName SampleNameLastChangedDateTime
----------- ---------- -----------------------------
1       One    2010-10-27 14:52:42.567
2       Two    2010-10-27 14:52:42.567
3       Three  2010-10-27 14:52:42.567
4       Foo    2010-10-27 14:52:42.587
5       Five   2010-10-27 14:52:42.587
*/

GO

UPDATE tblSample SET SampleName = 'Foo' 

SELECT SampleID, SampleName, SampleNameLastChangedDateTime FROM tblSample 
/*
SampleID  SampleName SampleNameLastChangedDateTime
----------- ---------- -----------------------------
1       Foo    2010-10-27 14:52:42.657
2       Foo    2010-10-27 14:52:42.657
3       Foo    2010-10-27 14:52:42.657
4       Foo    2010-10-27 14:52:42.587
5       Foo    2010-10-27 14:52:42.657
*/
GO

UPDATE tblSample SET SampleName = 'Not Prime' WHERE SampleID IN (1,4)

SELECT SampleID, SampleName, SampleNameLastChangedDateTime FROM tblSample
/*
SampleID  SampleName SampleNameLastChangedDateTime
----------- ---------- -----------------------------
1       Not Prime  2010-10-27 14:52:42.680
2       Foo        2010-10-27 14:52:42.657
3       Foo        2010-10-27 14:52:42.657
4       Not Prime  2010-10-27 14:52:42.680
5       Foo        2010-10-27 14:52:42.657
*/

--Clean up...
DROP TRIGGER dbo.tgr_tblSample_InsertUpdate
DROP TABLE tblSample

Utilisateur GBN avait suggéré ce qui suit:

IF EXISTS (
    SELECT
        *
    FROM
        INSERTED I
        JOIN
        DELETED D ON I.key = D.key
    WHERE
        D.valuecol <> I.valuecol --watch for NULLs!
    )
   blah

GBN la suggestion de l'utilisation d'un if (EXISTS( ...clause et de mettre de la logique dans le sens que SI une déclaration de lignes existent qui ont été modifiés pourraient travailler. Cette approche permettra d'incendie pour TOUTES les lignes comprises dans le déclencheur, même si seulement certaines lignes ont été réellement changé (ce qui peut être approprié pour votre solution, mais peut aussi ne pas être approprié si vous voulez seulement faire quelque chose pour les lignes où les valeurs modifiées.) Si vous avez besoin de faire quelque chose pour les lignes où un changement a eu lieu, vous avez besoin d'une logique différente dans votre SQL et qu'il a fournies.

Dans mes exemples ci-dessus, lorsque la mise à JOUR tblSample ENSEMBLE SampleName = 'Foo' émission de l'instruction et de la quatrième ligne est déjà 'foo', à l'aide de GBN l'approche de la mise à jour d'une "dernière modification datetime" colonne serait aussi de mise à jour de la quatrième ligne, qui ne serait pas appropriée dans ce cas.

Je pense que le code suivant est mieux que les exemples ci-dessus, car il se concentre sur les colonnes que vous souhaitez vérifier de manière concise et efficace.

Il détermine si une valeur a changé seulement dans les colonnes spécifiées. Je n'ai pas étudié ses performances en comparaison avec les autres solutions, mais il est bien dans ma base de données.

Il utilise l'EXCEPTION de l'ensemble de l'opérateur à renvoyer toutes les lignes de la requête à gauche ne sont pas également trouvé sur la droite de la requête. Ce code peut être utilisé dans d'INSERTION et de mise à JOUR des déclencheurs.

La "PrimaryKeyID" colonne de la clé primaire de la table (peut être plusieurs colonnes) et est nécessaire afin de permettre la correspondance entre les deux séries.

-- Only do trigger logic if specific field values change.
IF EXISTS(SELECT  PrimaryKeyID
                ,Column1
                ,Column7
                ,Column10
          FROM inserted
          EXCEPT
          SELECT PrimaryKeyID
                ,Column1
                ,Column7
                ,Column10
          FROM deleted )    -- Tests for modifications to fields that we are interested in
BEGIN
          -- Put code here that does the work in the trigger

END

Si vous souhaitez utiliser les lignes qui ont été modifiées dans la suite logique de déclencheur, j'ai l'habitude de mettre les résultats de l'EXCEPTION de la requête dans une variable de table qui peuvent être référencés plus tard.

J'espère que c'est de l'intérêt 🙂