[SQL Server] Tracer les opérations Insert, Update et Delete dans une table via un Trigger Exemple 1
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