Table – InstanceMilestones

Purpose

Used to track the milestones achieved by specific record Instances, Milestones are automatically created for instance state changes and the execution of Commands against the Instance. Milestones can also be manually added as required by workflow processing.

Application users can see a time ordered list of milestones that have been achieved on the currently selected record Instance.

Definition

CREATE TABLE [dbo].[InstanceMilestones](
	[InstanceGUID] [UNIQUEIDENTIFIER] NOT NULL,
	[AchievedOn] [DATETIME2](7) NOT NULL,
	[AchievedBy] [VARCHAR](100) NOT NULL,
	[Milestone] [VARCHAR](2000) NOT NULL,
 CONSTRAINT [PK_InstanceMilestones] PRIMARY KEY CLUSTERED 
(
	[InstanceGUID] ASC,
	[AchievedOn] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Column Definitions

The InstanceGUID is used to track which Instance record owns the milestone.

The AchievedOn maintains a timestamp of when the milestone was achieved and AchievedBy contains the name of the user who achieved it.

The Milestone contains a description of the milestone.

Key Functionality

The InstanceMilestones table has an optional trigger called InstanceMilestonesPull that adds new milestones to the InstanceChanges table

ALTER TRIGGER [dbo].[InstanceMilestonesPull]
ON [dbo].[InstanceMilestones] 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, 'Milestone'
		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, 'Milestone'
		FETCH NEXT FROM InsertedRecords INTO @InstanceGUID
	END

	CLOSE InsertedRecords;
	DEALLOCATE InsertedRecords;

END