So I have a table I am trying to create, but as I am working on this, I am worried that the idea is flawed.
Here's the SQL code to create the table so far:
CREATE TABLE #ConversionLog
(
EventTime DATETIME NULL,
EventType NVARCHAR(20) NULL,
RecordID INT NULL,
EventTarget NVARCHAR(255) NULL,
OldValue NVARCHAR(MAX) NULL,
NewValue NVARCHAR(MAX) NULL,
EventMessage NVARCHAR(MAX) NULL
)
The Columns are defined as:
- EventTime: The time of the log entry.
- EventType: The type of action that occured, such as insert, update, delete, error, message etc...
- RecordID: The primary key of the record that the event occured on.
- EventTarget: Contains the table name and column name the event is affecting in the format of TABLE.COLUMN.
- OldValue: The value before an update or the old value of a system setting that has changed if the entry is of that type.
- NewValue: The new value after the update or the new system setting value.
- EventMessage: Shows a message regarding the event. Could be many things.
So the problem is, since this is supposed to be used for logging events and value changes, I'm not sure what data type to use for OldValue and NewValue since in one case it could be a bit type, in another an int, or even a string value.
Is this going to work or is there a flexable data type that will basically just record the changing data as a string (which is fine since all of the data can be represented as a string) such as setting a True/On bit value as 1 and a False/Off value as 0 but in string format, not int. I'm just worried that the conversion might not work well. Since it's just a log, it's not really important that the data be convertable back to it's orignal type, it just needs to show what happened.
Any thoughts?