0%

Auditlog Masterdata

AUDIT LOG 

Since it’s not desirable to have transactional data in masterdata tables, we have the option to create an audit log that contains the audit trail of the changes made to the corresponding table, along with the option to add information such as: 

  • Username/email address of the person making the change (UpdatedBy)

  • Timestamp of the change (UpdateDateTime)

  • Rowkey of the change 

  • Transaction type (create/update/delete) 

  • SQL command 

To make use of the audit log, it’s required to create a new table.

CREATE TABLE [dbo].[DimEmployee_AUDIT](
	[EmployeeID] [int] NOT NULL,
	[Employee Name] [nvarchar](100) NULL,
	[Employment Date] [date] NULL,
	[Termination Date] [date] NULL,
	[Salary] [decimal](18, 6) NULL,
	[OrganizationKey] [int] NULL,

-- bottom 5 columns are used in the configuration

	[UpdatedBy] [nvarchar](255) NULL,
	[UpdateDateTime] [datetime2](6) NULL,
	[Rowkeys] [int] NULL,
	[Transaction type] [nvarchar](255) NULL,
	[SQL command] [nvarchar](400) NULL
)
 
 

Note that the bottom 5 columns are used in the configuration of our audit log in the accoMASTERDATA visual connection configuration as seen in : Audit log configuration. 

0%