[SQL Server] Tracer les opérations Insert, Update et Delete dans une table via un Trigger

CREATE TRIGGER [dbo].[TRG_TRACE_TABLE_1]

   ON [dbo].[TABLE_1]

   AFTER INSERT, UPDATE, DELETE

AS

BEGIN

      DECLARE @Desc_Action VARCHAR(50)

     

      IF EXISTS(SELECT * FROM inserted)

            BEGIN

                  IF EXISTS(SELECT * FROM deleted)

                        BEGIN

                             SELECT @Desc_Action = 'UPDATE'

                             --Sauvegarder les donnéess avant modification

                             INSERT INTO dbo.TABLE_1_HISTORIQUE

                             SELECT champ_1

                             ,champ_2

                             ,champ_3

                             ,GETDATE() Date_Action

                             ,SYSTEM_USER Utilisateur_Action

                             ,@Desc_Action Desc_Action

                             FROM deleted

                        END  

                  ELSE

                        BEGIN

                             SELECT @Desc_Action = 'INSERT'

                        END

                          

                  INSERT INTO dbo.TABLE_1_HISTORIQUE

                  SELECT champ_1

                  ,champ_2

                  ,champ_3

                  ,GETDATE() Date_Action

                  ,SYSTEM_USER Utilisateur_Action

                  ,@Desc_Action Desc_Action

                  FROM INSERTED

  

            END

      ELSE

            BEGIN

           

                  IF EXISTS(SELECT * FROM deleted)

                        BEGIN

                             SELECT @Desc_Action = 'DELETE'

                             INSERT INTO dbo.TABLE_1_HISTORIQUE

                             SELECT champ_1

                             ,champ_2

                             ,champ_3

                             ,GETDATE() Date_Action

                             ,SYSTEM_USER Utilisateur_Action

                             ,@Desc_Action Desc_Action

                             FROM deleted

                        END  

                  ELSE

                        BEGIN

                             SELECT @Desc_Action = NULL;

                        END

      

            END

     

END

Ajouter un Commentaire


Code de sécurité
Rafraîchir