Hi,
I Created , in sqlServer, an AFTER UPDATE trigger on the Table 'DEP_MARCHE' that will insert into my audit table 'AuditDepMarche' two rows: 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 fields of DEP_MARCHE ( DEP_MARCHE and AuditDepMarche have the same structure). Also I created a 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 instert something intothe DEP_MARCHE table (One row for by insert trigger, and two rows; old & new values, duplicated => 4 rows )
this is my code, I would appriciate 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
