SQL SERVER FULL TABLE CHANGE LOG

علی ذوالفقار
1404/02/28 07:40:33 (31)

CREATE A TABLE TO STORE LOGS :

CREATE TABLE [TBL_DATACHANGE_LOGS] (
  [ID]            INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  [TABLE_NAME]    NVARCHAR(255) COLLATE ARABIC_CI_AS  NULL, 
  [INSERTED_JSON] NVARCHAR(MAX) COLLATE ARABIC_CI_AS  NULL,
  [DELETED_JSON]  NVARCHAR(MAX) COLLATE ARABIC_CI_AS  NULL,  
  [NOTE]          NVARCHAR(MAX) COLLATE ARABIC_CI_AS  NULL,
  [CREATE_DATE]   DATETIME2(7) DEFAULT GETDATE()      NULL
)

CREATE A TRIGGER TO STORE CHANGE LOG :

INSERT INTO TBL_DATACHANGE_LOGS
  ( TABLE_NAME , INSERTED_JSON , DELETED_JSON , NOTE  )
VALUES
  (   'TABLE_1' 
    , (SELECT * FROM INSERTED FOR JSON AUTO) 
    , (SELECT * FROM DELETED  FOR JSON AUTO)
    , 'NOTES...' 
  )

Back