CODEWITHSUNDEEP
sql-servertriggers
assia
assia

Reputation: 1

Update trigger firing after insert

I created an AFTER UPDATE trigger on the table DEP_MARCHE that will insert two rows into my audit table AuditDepMarche: one row for the old values (from the virtual table DELETED) and the second contains the new values (from the virtual table INSERTED) of all columns of DEP_MARCHE (DEP_MARCHE and AuditDepMarche have the same structure). Also I created an AFTER INSERT trigger that adds one row to my audit table.

The problem is that when I insert something into DEP_MARCHE the AFTER UPDATE trigger is fired, consequently three rows are added to my audit table (one row added by the AFTER INSERT trigger and the two others by the AFTER UPDATE trigger )

I searched in the internet, the solution I found is: to create one trigger for both (AFTER INSERT, UPDATE) and test on the virtual table DELETED if it's empty then I run the queries for after Insert trigger, else I run the queries of after update trigger, something like this (it didn't work for me though) :

CREATE TRIGGER foo_AlteredRecord 
ON [dbo].[Foo]
AFTER INSERT, UPDATE
AS
BEGIN
    DECLARE @DelCount int;
    DECLARE @InsCount int;

    SELECT @InsCount = COUNT(Col1) FROM INSERTED;
    SELECT @DelCount = COUNT(Col1) FROM DELETED;

    IF @InsCount > 0 AND @DelCount = 0
    BEGIN
        -- At least 1 row inserted. Your Insert Trigger logic here
    END
    ELSE IF @DelCount > 0 AND @InsCount > 0
    BEGIN
        -- old row deleted, new row inserted; both indicates an update.
        -- your update logic here.  
    END
END

After I implemented this logic to my code, it began to add not 3 rows, but 5 rows into my audit table whenever I insert something into the DEP_MARCHE table (One row for by insert trigger, and two rows; old & new values, duplicated => 4 rows )

This is my code, I would appreciate any help

Drop trigger if exists DepMarcheAudit_UPDATE_INSERT
go
CREATE TRIGGER DepMarcheAudit_UPDATE_INSERT
ON DEP_MARCHE
AFTER UPDATE, INSERT
AS
BEGIN

   Declare @DelCount int;
   Declare @InsCount int;

   SELECT @InsCount = Count(*) FROM INSERTED;
   SELECT @DelCount = Count(*) FROM DELETED;

 -----------AFTER UPDATE TRIGGER TRANSACTIONS---------
IF @InsCount > 0 AND @DelCount > 0
   BEGIN
   DECLARE @AUDIT_OPERATION_OLD VARCHAR(50)
   SET @AUDIT_OPERATION_OLD = 'mise à jour (Old values)'

   DECLARE @AUDIT_OPERATION_NEW VARCHAR(50)
   SET @AUDIT_OPERATION_NEW = 'mise à jour (New values)'

   declare @StartTime datetimeoffset(7) = sysdatetimeoffset();

   INSERT INTO [dbo].[DepMarcheAudit](
   [CODE_MARCHE]
   ,[AUDIT_OPERATION_TYPE]
   ,[AUDIT_OPERATION_DATE]
   ,[AUDIT_OPERATION_TIME]
   ,[AUDIT_ID_USER]
         ,[LIBELLE_MARCHE]
         ,[CODE_FOURNISSEUR]
         ,[NUMERO_MARCHE]
         ,[OBSERVATION_MARCHE]
         ,[CODE_NATURE]
         ,[CODE_AO]
         ,[DOC_CONTRAT]
         ,[IS_DEPENSE_SIMPLIFIEE],
         [startTime]
         )
   SELECT 
   [CODE_MARCHE]
   ,@AUDIT_OPERATION_OLD
   ,GETDATE()
   ,(CONVERT([time],getdate(),0))
   ,HOST_NAME()
         ,[LIBELLE_MARCHE]
         ,[CODE_FOURNISSEUR]
         ,[NUMERO_MARCHE]
         ,[OBSERVATION_MARCHE]
         ,[CODE_NATURE]
         ,[CODE_AO]
         ,[DOC_CONTRAT]
         ,[IS_DEPENSE_SIMPLIFIEE]
         ,@StartTime
   FROM DELETED; 

   INSERT INTO [dbo].[DepMarcheAudit](
   [CODE_MARCHE]
   ,[AUDIT_OPERATION_TYPE]
   ,[AUDIT_OPERATION_DATE]
   ,[AUDIT_OPERATION_TIME]
   ,[AUDIT_ID_USER]
         ,[LIBELLE_MARCHE]
         ,[CODE_FOURNISSEUR]
         ,[NUMERO_MARCHE]
         ,[OBSERVATION_MARCHE]
         ,[CODE_NATURE]
         ,[CODE_AO]
         ,[DOC_CONTRAT]
         ,[IS_DEPENSE_SIMPLIFIEE]
         ,[startTime]
         )



   SELECT 



   [CODE_MARCHE]
   ,@AUDIT_OPERATION_NEW
   ,getdate()
   ,(CONVERT([time],getdate(),0))
   ,HOST_NAME()
         ,[LIBELLE_MARCHE]
         ,[CODE_FOURNISSEUR]
         ,[NUMERO_MARCHE]
         ,[OBSERVATION_MARCHE]
         ,[CODE_NATURE]
         ,[CODE_AO]
         ,[DOC_CONTRAT]
         ,[IS_DEPENSE_SIMPLIFIEE]
         ,@StartTime
   FROM INSERTED;
   END

--------------------AFTER INSERT TRIGGER TRANSACTION-----------------------------
ELSE IF @InsCount > 0 AND @DelCount = 0
   
   BEGIN
   
   DECLARE @AUDIT_OPERATION VARCHAR(50)
   SET @AUDIT_OPERATION = 'Insertion'
   INSERT INTO [dbo].[DepMarcheAudit](
   [CODE_MARCHE]
   ,[AUDIT_OPERATION_TYPE]
   ,[AUDIT_OPERATION_DATE]
   ,[AUDIT_OPERATION_TIME]
   ,[AUDIT_ID_USER]
         ,[LIBELLE_MARCHE]
         ,[CODE_FOURNISSEUR]
         ,[NUMERO_MARCHE]
         ,[OBSERVATION_MARCHE]
         ,[CODE_NATURE]
         ,[CODE_AO]
         ,[DOC_CONTRAT]
         ,[IS_DEPENSE_SIMPLIFIEE]
         ,[startTime])

         SELECT 

   [CODE_MARCHE]
   ,@AUDIT_OPERATION
   ,GETDATE()
   ,(CONVERT([time],getdate(),0))
   ,HOST_NAME()
   ,[LIBELLE_MARCHE]
   ,[CODE_FOURNISSEUR]
   ,[NUMERO_MARCHE]
   ,[OBSERVATION_MARCHE]
   ,[CODE_NATURE]
   ,[CODE_AO]
   ,[DOC_CONTRAT]
   ,[IS_DEPENSE_SIMPLIFIEE]
   , sysdatetimeoffset()
FROM INSERTED; 

END
END
GO

Upvotes: 0

Views: 1631

Answers (1)

Randy in Marin
Randy in Marin

Reputation: 1143

You can use one trigger for inserts, updates, and deletes because you are doing the same thing for all. The only difference appears to be a message about it being an insert, update(before), update(after), or a delete. I don't see a delete, but I included it because it's no additional work.

If it's a delete, the select from INSERTED will insert nothing into the audit table. If it's an insert, the select from DELETED will insert nothing into the audit table. If it's an update, both inserts will insert into the audit table.

CREATE TRIGGER DepMarcheAudit_UPDATE_INSERT_DELETE
ON DEP_MARCHE
AFTER UPDATE, INSERT, DELETE
AS
BEGIN

    SET NOCOUNT ON

    DECLARE @DelCount int;
    DECLARE @InsCount int;

    SELECT @InsCount = Count(*) FROM INSERTED;
    SELECT @DelCount = Count(*) FROM DELETED;

    IF @InsCount = 0 AND @DelCount = 0 RETURN

    DECLARE @AUDIT_OPERATION_OLD VARCHAR(50);
    DECLARE @AUDIT_OPERATION_NEW VARCHAR(50);

    IF @InsCount > 0 AND @DelCount > 0 
    BEGIN
       SET @AUDIT_OPERATION_OLD = 'Update (Old values)';
       SET @AUDIT_OPERATION_NEW = 'Update (New values)';
    END
    ELSE IF @InsCount > 0
    BEGIN
       SET @AUDIT_OPERATION_OLD = NULL;
       SET @AUDIT_OPERATION_NEW = 'Insert (New values)';
    END
    ELSE
    BEGIN
       SET @AUDIT_OPERATION_OLD = 'Delete (Old values)';
       SET @AUDIT_OPERATION_NEW = NULL;
    END;

    INSERT INTO [dbo].[DepMarcheAudit](
       [CODE_MARCHE]
       ,[AUDIT_OPERATION_TYPE]
       ...
       )
    SELECT 
       [CODE_MARCHE]
       ,@AUDIT_OPERATION_OLD
       ...
    FROM DELETED; 

    INSERT INTO [dbo].[DepMarcheAudit](
       [CODE_MARCHE]
       ,[AUDIT_OPERATION_TYPE]
       ...
       )
    SELECT 
       [CODE_MARCHE]
       ,@AUDIT_OPERATION_NEW
       ...
    FROM INSERTED; 

    RETURN;

END

Do remember to delete or disable the other triggers if they are redundant.

Upvotes: 1

Related Questions