Purpose
Tracks errors, or warnings, that occur as commands are executed on a specific instance record.
This functionality is designed for tracking complex command processing where events may be raised on the Instance, or its children. It brings all such events together in a single stream to make it easier to diagnose errors.
Definition
CREATE TABLE [dbo].[InstanceEvents](
[InstanceGUID] [UNIQUEIDENTIFIER] NOT NULL,
[LoggedOn] [DATETIME2](7) NOT NULL,
[Event] [VARCHAR](MAX) NOT NULL,
CONSTRAINT [PK_InstanceEvents] PRIMARY KEY CLUSTERED
(
[InstanceGUID] ASC,
[LoggedOn] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]Column Definitions
The InstanceGUID is used to track which Instance record owns the event.
The LoggedOn maintains a timestamp of when the event occurred.
The Event contains a record of the event type and its context which is created by the code that logs the event.
Key Functionality
Table has an optional trigger called InstanceEventsPull that will send copies of the InstanceEvents to the InstanceChanges table if it should be necessary to transport them to other data storage.
CREATE OR ALTER TRIGGER [dbo].[InstanceEventsPull]
ON [dbo].[InstanceEvents] FOR INSERT, UPDATE, DELETE
AS
BEGIN
DECLARE @InstanceGUID AS UNIQUEIDENTIFIER
DECLARE DeletedRecords CURSOR FOR
SELECT InstanceGUID FROM Deleted
OPEN DeletedRecords
FETCH NEXT FROM DeletedRecords INTO @InstanceGUID
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE dbo.GI_InstanceChanged @InstanceGUID, 'Event'
FETCH NEXT FROM DeletedRecords INTO @InstanceGUID
END
CLOSE DeletedRecords;
DEALLOCATE DeletedRecords;
DECLARE InsertedRecords CURSOR FOR
SELECT InstanceGUID FROM Inserted
OPEN InsertedRecords
FETCH NEXT FROM InsertedRecords INTO @InstanceGUID
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE dbo.GI_InstanceChanged @InstanceGUID, 'Event'
FETCH NEXT FROM InsertedRecords INTO @InstanceGUID
END
CLOSE InsertedRecords;
DEALLOCATE InsertedRecords;
END