What you want to monitor and capture, as well as your RDBMS will deturmin how you create your table or tables. These tables are written from the SQL Server point of view. You may need to adjust these for your RDBMS.
If you wanted to capture schema changes then a table something like this would be a good starting point.
CREATE TABLE SchemaMonitorObject
(LogId int IDENTITY(1,1) PRIMARY KEY,
ChangeTypeId int, /*1 Create Table, 2, Add Column, 3, Drop Column, 4 Drop Table*/
CREATE TABLE SchemaMonitorColumn
(LogID int PRIMARY KEY,
If you needed to capture data changes then a table somethign like this would be a good starting point.
CREATE TABLE LogDataChange
(LogId bigint IDENTITY(-10000000, 1) PRIMARY KEY,
TableKeyId INT, /*This would be the unique identifier for the record of the source table which is being modified, typically the primary key from that table.*/
OldValue sql_variant, /*Using a trigger to capture the change the old value from the deleted virtual table. This will give you all the prior values in the log table, and have the current value in the table. We use the sql_variant data type so that all data types can be stored in a single column.*/
WasRecordDeleted bit /*If the record was deleted this should be set to 1 so that it’s known that the record was deleted at this point.*/)