Déclencheur Insert Update comment déterminer si l'insertion ou la mise à jour

Question

J'ai besoin d'écrire un trigger d'insertion et de mise à jour sur la table A qui supprimera toutes les lignes de la table B dont une colonne (disons Desc) a des valeurs semblables à la valeur insérée/mise à jour dans la colonne de la table A (disons Col1). Comment pourrais-je l'écrire de manière à pouvoir gérer les cas de mise à jour et d'insertion ? Comment puis-je déterminer si le déclencheur est exécuté pour une mise à jour ou une insertion ?

Answer 1

Si c'est MS SQL Server...

Les déclencheurs ont des caractéristiques particulières INSERTED et DELETED pour suivre les données "avant" et "après". Vous pouvez donc utiliser quelque chose comme IF EXISTS (SELECT * FROM DELETED) pour détecter une mise à jour. Vous avez seulement des lignes dans DELETED lors de la mise à jour, mais il y a toujours des rangées dans INSERTED .

Cherchez "inséré" dans CREATE TRIGGER

Edit, 23 Nov 2011

Après commentaire, cette réponse est seulement pour INSERTED et UPDATED déclencheurs.
De toute évidence, les déclencheurs DELETE ne peuvent pas avoir "toujours des rangs en INSERTED "comme je l'ai dit plus haut

Answer 2

CREATE TRIGGER dbo.TableName_IUD
ON dbo.TableName
AFTER INSERT, UPDATE, DELETE
AS 
BEGIN
    SET NOCOUNT ON;

    --
    -- Check if this is an INSERT, UPDATE or DELETE Action.
    -- 
    DECLARE @action as char(1);

    SET @action = 'I'; -- Set Action to Insert by default.
    IF EXISTS(SELECT * FROM DELETED)
    BEGIN
        SET @action = 
            CASE
                WHEN EXISTS(SELECT * FROM INSERTED) THEN 'U' -- Set Action to Updated.
                ELSE 'D' -- Set Action to Deleted.       
            END
    END
    ELSE 
        IF NOT EXISTS(SELECT * FROM INSERTED) RETURN; -- Nothing updated or inserted.

    ...

    END

Answer 3

Beaucoup de ces suggestions ne tiennent pas compte du fait que vous exécutez une instruction de suppression qui ne supprime rien.
Supposons que vous essayez de supprimer un ID qui correspond à une valeur qui n'existe pas dans la table.
Votre déclencheur est toujours appelé mais il n'y a rien dans les tables Deleted ou Inserted.

Utilisez ceci pour être sûr :

--Determine if this is an INSERT,UPDATE, or DELETE Action or a "failed delete".
DECLARE @Action as char(1);
    SET @Action = (CASE WHEN EXISTS(SELECT * FROM INSERTED)
                         AND EXISTS(SELECT * FROM DELETED)
                        THEN 'U'  -- Set Action to Updated.
                        WHEN EXISTS(SELECT * FROM INSERTED)
                        THEN 'I'  -- Set Action to Insert.
                        WHEN EXISTS(SELECT * FROM DELETED)
                        THEN 'D'  -- Set Action to Deleted.
                        ELSE NULL -- Skip. It may have been a "failed delete".   
                    END)

Merci à @KenDog et @Net_Prog pour leurs réponses.
J'ai construit ça à partir de leurs scripts.

Answer 4

Après de nombreuses recherches, je n'ai pas pu trouver un exemple exact d'un déclencheur SQL Server unique qui gère les trois (3) conditions des actions de déclenchement INSERT, UPDATE et DELETE. J'ai finalement trouvé une ligne de texte qui parlait du fait que lorsqu'un DELETE ou UPDATE se produit, la table commune DELETED contiendra un enregistrement pour ces deux actions. Sur la base de cette information, j'ai ensuite créé une petite routine Action qui détermine pourquoi le déclencheur a été activé. Ce type d'interface est parfois nécessaire lorsqu'il y a à la fois une configuration commune et une action spécifique à réaliser sur un déclencheur INSERT vs UPDATE. Dans ces cas, créer un déclencheur séparé pour l'UPDATE et l'INSERT deviendrait un problème de maintenance. (c'est-à-dire que les deux déclencheurs ont été mis à jour correctement pour la correction nécessaire de l'algorithme de données communes).

À cette fin, j'aimerais vous donner le code d'événement multi-trigger suivant pour gérer INSERT, UPDATE, DELETE dans un trigger pour un serveur Microsoft SQL.

CREATE TRIGGER [dbo].[INSUPDDEL_MyDataTable]
ON [dbo].[MyDataTable] FOR INSERT, UPDATE, DELETE
AS 

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with caller queries SELECT statements.
-- If an update/insert/delete occurs on the main table, the number of records affected
-- should only be based on that table and not what records the triggers may/may not
-- select.
SET NOCOUNT ON;

--
-- Variables Needed for this Trigger
-- 
DECLARE @PACKLIST_ID varchar(15)
DECLARE @LINE_NO smallint
DECLARE @SHIPPED_QTY decimal(14,4)
DECLARE @CUST_ORDER_ID varchar(15)
--
-- Determine if this is an INSERT,UPDATE, or DELETE Action
-- 
DECLARE @Action as char(1)
DECLARE @Count as int
SET @Action = 'I' -- Set Action to 'I'nsert by default.
SELECT @Count = COUNT(*) FROM DELETED
if @Count > 0
    BEGIN
        SET @Action = 'D' -- Set Action to 'D'eleted.
        SELECT @Count = COUNT(*) FROM INSERTED
        IF @Count > 0
            SET @Action = 'U' -- Set Action to 'U'pdated.
    END

if @Action = 'D'
    -- This is a DELETE Record Action
    --
    BEGIN
        SELECT @PACKLIST_ID =[PACKLIST_ID]
                    ,@LINE_NO = [LINE_NO]
        FROM DELETED

        DELETE [dbo].[MyDataTable]
        WHERE [PACKLIST_ID]=@PACKLIST_ID AND [LINE_NO]=@LINE_NO
    END
 Else
    BEGIN
            --
            -- Table INSERTED is common to both the INSERT, UPDATE trigger
            --
            SELECT @PACKLIST_ID =[PACKLIST_ID]
                ,@LINE_NO = [LINE_NO]
                ,@SHIPPED_QTY =[SHIPPED_QTY]
                ,@CUST_ORDER_ID = [CUST_ORDER_ID]
            FROM INSERTED 

         if @Action = 'I'
            -- This is an Insert Record Action
            --
            BEGIN
                INSERT INTO [MyChildTable]
                    (([PACKLIST_ID]
                    ,[LINE_NO]
                    ,[STATUS]
                VALUES
                    (@PACKLIST_ID
                    ,@LINE_NO
                    ,'New Record'
                    )
            END
        else
            -- This is an Update Record Action
            --
            BEGIN
                UPDATE [MyChildTable]
                    SET [PACKLIST_ID] = @PACKLIST_ID
                          ,[LINE_NO] = @LINE_NO
                          ,[STATUS]='Update Record'
                WHERE [PACKLIST_ID]=@PACKLIST_ID AND [LINE_NO]=@LINE_NO
            END
    END

Answer 5

Un problème potentiel avec les deux solutions proposées est que, selon la façon dont elles sont écrites, une requête de mise à jour peut mettre à jour zéro enregistrement et une requête d'insertion peut insérer zéro enregistrement. Dans ces cas, les jeux d'enregistrements Inserted et Deleted seront vides. Dans de nombreux cas, si les jeux d'enregistrements Inserted et Deleted sont vides, vous voudrez peut-être simplement quitter le déclencheur sans rien faire.