[SQL Server] Tracer les opérations Insert, Update et Delete dans une table via un Trigger Exemple 2
-- =============================================
-- Author: Zouhaier KHARROUBI
-- Create date: 18/11/2024
-- Description: Tracer les mises à jours d'une colonne d'une table
-- =============================================
ALTER TRIGGER trg_MesTracesSQL_TraceMaj
ON [dbo].[MaTableAtracer]
AFTER INSERT,UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
DECLARE @SQLBuffer nvarchar(4000)
DECLARE @buffer TABLE (
EventType nvarchar(30),
Parameters int,
EventInfo nvarchar(4000)
)
IF UPDATE( [MonChampsAtracer])
begin
IF EXISTS(SELECT * FROM deleted WHERE [MonChampsAtracer]=47) AND EXISTS(SELECT * FROM inserted WHERE [MonChampsAtracer]=4)
BEGIN
INSERT @buffer
EXEC sp_executesql N'DBCC INPUTBUFFER(@@spid) WITH NO_INFOMSGS'
INSERT INTO [dbo].[MesTracesSQL]([date_lig],[commande_sql])
SELECT GETDATE(), EventInfo
FROM @buffer
END
END
END