/* -------------------------------------------------------------------
	Table
		MachineDomain
	
	Documentation
		Project: thisProject
		Aspect: Core
		Prepared by: thisOrganisationalUnit
		For: thatOrganistionalUnit 
	
	Description
	
	Stores the descriptor for each machine to be supported by the 
	suite. 
	
	The [Serial] column is an alias for the [Id] and is a unique
	serial number from the manufacturer. Join to the record on the
	[Id], not the [Serial].	
   -----------------------------------------------------------------*/
CREATE TABLE [dbo].[MachineDomain]
(
	[Active] [datetime] NULL,
	[Archive] [datetime] NULL,	
	[ArchiveInsert] [datetime] NULL,
	[AuditReferenceInsert] [uniqueidentifier] NULL,
	[AuditReferenceUpdate] [uniqueidentifier] NULL,	
	[Description] [nvarchar] (900) NULL,
	[DomainId] [int] NOT NULL IDENTITY(1, 1),
	[Id] [int] NULL,
	[ModelCategoryDomainId] [int] NOT NULL,
	[Name] [nvarchar] (450) NOT NULL,
	[Serial] [nchar] (24) NULL	
) 
ON [PRIMARY]
GO

 
/* -------------------------------------------------------------------
	Trigger
		MachineDomainInsteadOfInsert
	
	Documentation
		Project: thisProject
		Aspect: Core
		Prepared by: thisOrganisationalUnit
		For: thatOrganistionalUnit 
   -----------------------------------------------------------------*/
ALTER TRIGGER [MachineDomainInsteadOfInsert] 
ON [dbo].[MachineDomain] 
INSTEAD OF INSERT AS
BEGIN
	SET NOCOUNT ON
	DECLARE @Active [datetime]
	DECLARE @AccountId [int]
	DECLARE @Archive [datetime]
	DECLARE @AuditCategoryId [int]
	DECLARE @AuditReferenceInsert [uniqueidentifier]
	DECLARE @AuditReferenceUpdate [uniqueidentifier]	
	DECLARE @Count [int]
	DECLARE @CurrentTimestamp [datetime]
	DECLARE @DomainId [int]
	DECLARE @Error [int]
	DECLARE @MaximumTimestamp [datetime]
	DECLARE @MessageAlias [nchar] (24)
	DECLARE @MessageObject [nvarchar] (450)			
	DECLARE @MessageText [nvarchar] (400)
	DECLARE @Return [int]
/* -------------------------------------------------------------------
	Get constants.  
------------------------------------------------------------------- */	
	SET @MessageObject = LOWER(N'['+@@SERVERNAME+N'].['+DB_NAME()+N'].['+SESSION_USER+N'].['+OBJECT_NAME(@@PROCID)+N']')
/* -------------------------------------------------------------------
	INSERT multiple records in a single batch is not supported.
------------------------------------------------------------------- */
	SELECT 
		@Count = COUNT(*) 
	FROM 
		INSERTED
	SET @Error = @@ERROR		
	IF  (@Error <> 0) 
	BEGIN
		SET @MessageAlias = N'TOU56301'
		SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default)
		RAISERROR (@MessageText,10,1) WITH SETERROR
		RETURN
	END
	IF  (@Count = 0) 
	BEGIN
		SET @MessageAlias = N'TOU56302'
		SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default)
		RAISERROR (@MessageText,10,1) WITH SETERROR
		RETURN
	END		
	IF  (@Count > 1) 
	BEGIN
		SET @MessageAlias = N'TOU56303'
		SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default)
		RAISERROR (@MessageText,10,1) WITH SETERROR
		RETURN
	END			
/* -------------------------------------------------------------------
	Get values from the INSERTED table that will be referenced several
	times in the following checking			
------------------------------------------------------------------- */
	SET @Active = NULL
	SET @Archive = NULL
	SET @AuditReferenceInsert = NULL
	SELECT
		@Active = [Active],
		@Archive = [Archive],
		@AuditReferenceInsert = [AuditReferenceInsert]
	FROM 
		INSERTED
	SET @Error = @@ERROR
	IF (@Error <> 0) 
	BEGIN
		SET @MessageAlias = N'TOU56304'
		SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default)
		RAISERROR (@MessageText,10,1) WITH SETERROR
		RETURN
	END	
/* -------------------------------------------------------------------
	Check the session token is current and a valid account is 
	executing the trigger.
------------------------------------------------------------------- */	
	SET @Return = NULL
	EXECUTE @Return = [dbo].[CheckAccess]
		@AccountId = @AccountId OUTPUT,
		@AuditAccessAlias = N'INSERT',
		@AuditCategoryAlias = N'MACHINE',		
		@AuditCategoryId = @AuditCategoryId OUTPUT,
		@AuditDatabaseReference = @AuditReferenceUpdate OUTPUT,
		@CurrentTimestamp = @CurrentTimestamp OUTPUT,
		@MaximumTimestamp = @MaximumTimestamp OUTPUT,
		@MessageAlias = @MessageAlias OUTPUT,
		@Reference = @AuditReferenceInsert 
	SET @Error = @@ERROR
	IF (@Error <> 0 OR @Return IS NULL OR @Return <> 0)
	BEGIN
		SET @MessageAlias = ISNULL(LTRIM(RTRIM(@MessageAlias)),N'TOU59998')
		SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default)
		RAISERROR (@MessageText,10,1) WITH SETERROR, LOG
		RETURN
	END	
/* -------------------------------------------------------------------
	Set default values if not supplied on the INSERTED record.			
------------------------------------------------------------------- */
	SET @Active = ISNULL(@Active, @CurrentTimestamp)
	SET @Archive = ISNULL(@Archive, @MaximumTimestamp)
/* -------------------------------------------------------------------
	Check there is no inconsistant temporal overlap. 
------------------------------------------------------------------- */
	IF (@Active < @CurrentTimestamp)
	BEGIN
		SET @MessageAlias = N'TOU56305'
		SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default)
		RAISERROR (@MessageText,10,1) WITH SETERROR
		RETURN
	END	
	IF (@Archive < @Active)
	BEGIN
		SET @MessageAlias = N'TOU56306'
		SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default)
		RAISERROR (@MessageText,10,1) WITH SETERROR
		RETURN
	END	
/* -------------------------------------------------------------------
	Check the new descriptor is unique. If it isn't unique it will
	require an UPDATE not an INSERT. 
------------------------------------------------------------------- */
	BEGIN
		IF EXISTS 
		(
			SELECT 
				* 
			FROM 
				[dbo].[MachineDomain] AS [MachineDomain]
				INNER JOIN INSERTED AS [Inserted]
					ON [MachineDomain].[Name] = [Inserted].[Name]			
		)
		BEGIN
			SET @MessageAlias = N'TOU56307'
			SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,N'[Name]')
			RAISERROR (@MessageText,10,1) WITH SETERROR
			RETURN		
		END	
	END
	BEGIN
		IF EXISTS 
		(
			SELECT 
				* 
			FROM 
				[dbo].[MachineDomain] AS [MachineDomain]
				INNER JOIN INSERTED AS [Inserted]
					ON [MachineDomain].[Serial] = [Inserted].[Serial]			
		)
		BEGIN
			SET @MessageAlias = N'TOU56307'
			SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,N'[Serial]')
			RAISERROR (@MessageText,10,1) WITH SETERROR
			RETURN		

		END	
	END	
/* -------------------------------------------------------------------
	INSERT the new record.
------------------------------------------------------------------- */	
	INSERT [dbo].[MachineDomain] 
	(
		[Active],
		[Archive],
		[ArchiveInsert],
		[AuditReferenceInsert],
		[AuditReferenceUpdate],	
		[Description],	
		[ModelCategoryDomainId],
		[Name],
		[Serial]
	)
	SELECT 
		@Active,
		@Archive,
		@Archive,
		@AuditReferenceInsert,
		@AuditReferenceUpdate,
		[Description],
		[ModelCategoryDomainId],		
		[Name],		
		[Serial]		
	FROM 
		INSERTED AS [Inserted]
	SET @Error = @@ERROR
	SET @DomainId = SCOPE_IDENTITY()
	IF (@Error <> 0 OR @DomainId IS NULL OR @DomainId = 0)
	BEGIN
		SET @MessageAlias = N'TOU56309'
		SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default)
		RAISERROR (@MessageText,10,1) WITH SETERROR
		RETURN
	END	
	UPDATE [dbo].[MachineDomain]
	SET	[Id] = @DomainId
	WHERE [DomainId] = @DomainId
	SET @Error = @@ERROR
	IF (@Error <> 0)
	BEGIN
		SET @MessageAlias = N'TOU56310'
		SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default)
		RAISERROR (@MessageText,10,1) WITH SETERROR
		RETURN
	END		
END
GO 

 
/* -------------------------------------------------------------------
	Trigger
		MachineDomainInsteadOfUpdate
	
	Documentation
		Project: thisProject
		Aspect: Core
		Prepared by: thisOrganisationalUnit
		For: thatOrganistionalUnit 
   -----------------------------------------------------------------*/
ALTER TRIGGER [MachineDomainInsteadOfUpdate] 
ON [dbo].[MachineDomain] 
INSTEAD OF UPDATE AS
BEGIN
	SET NOCOUNT ON
	DECLARE @AccountId [int]
	DECLARE @AuditAccessAlias [nchar] (24)
	DECLARE @AuditCategoryId [int]
	DECLARE @AuditDatabaseReference [uniqueidentifier]	
	DECLARE @Count [int]
	DECLARE @CountDistinct [int]
	DECLARE @CurrentTimestamp [datetime]
	DECLARE @DefaultAuditReferenceUpdate [uniqueidentifier]		
	DECLARE @DeletedActive [datetime]
	DECLARE @DeletedArchive [datetime]
	DECLARE @DeletedAuditReferenceInsert [uniqueidentifier]
	DECLARE @DeletedAuditReferenceUpdate [uniqueidentifier]
	DECLARE @DeletedDomainId [int]
	DECLARE @DeletedId [int]
	DECLARE @Error [int]	
	DECLARE @InsertedActive [datetime]
	DECLARE @InsertedArchive [datetime]
	DECLARE @InsertedAuditReferenceInsert [uniqueidentifier]
	DECLARE @InsertedAuditReferenceUpdate [uniqueidentifier]
	DECLARE @MaximumTimestamp [datetime]
	DECLARE @MessageAlias [nchar] (24)
	DECLARE @MessageObject [nvarchar] (450)			
	DECLARE @MessageText [nvarchar] (400)
	DECLARE @Return [int]
	DECLARE @ReturnDomainId [int]
/* -------------------------------------------------------------------
	Get constants  
------------------------------------------------------------------- */	
	SET @MessageObject = LOWER(N'['+@@SERVERNAME+N'].['+DB_NAME()+N'].['+SESSION_USER+N'].['+OBJECT_NAME(@@PROCID)+N']')
/* -------------------------------------------------------------------
	UPDATE multiple records in a single batch is not supported, 
	however UPDATE is allowed by specifying the [DomainId] or [Id] of
	the record.
		
	If the [DomainId] is specified on the UPDATE constraint, it must 
	be the currently active record of the [Id] group.
		
	If the [Id] is specified on the UPDATE constraint, multiple
	records may be SELECTed but only the currently active record from
	the [Id] group will be UPDATEd.
------------------------------------------------------------------- */	
	SELECT 
		@Count = COUNT(*) 
	FROM 
		INSERTED
	SET @Error = @@ERROR		
	IF  (@Error <> 0) 
	BEGIN
		SET @MessageAlias = N'TOU56301'
		SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default)
		RAISERROR (@MessageText,10,1) WITH SETERROR
		RETURN
	END
	IF  (@Count = 0) 
	BEGIN
		SET @MessageAlias = N'TOU56302'
		SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default)
		RAISERROR (@MessageText,10,1) WITH SETERROR
		RETURN
	END		
	IF  (@Count > 1) 
	BEGIN
		SELECT 
			@CountDistinct = COUNT(DISTINCT [Id]) 
		FROM 
			INSERTED
		SET @Error = @@ERROR		
		IF  (@Error <> 0 OR @CountDistinct > 1) 
		BEGIN
			SET @MessageAlias = N'TOU56303'
			SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default)
			RAISERROR (@MessageText,10,1) WITH SETERROR
			RETURN
		END	
	END
/* -------------------------------------------------------------------
	Get values from the DELETED and INSERTED tables that will be 
	referenced several times in the following checking.
	
	As a consequence of allowing UPDATE based on [DomainId] or [Id],
	[AuditDatabaseStatic].[Reference] has to be included in the 
	SELECT here, rather than waiting for it to be returned from the 
	call to [CheckAccess] as happens in the INSERT triggers and all
	other procedures, so that the correct record can be retrieved.
------------------------------------------------------------------- */	
	SELECT
		@DeletedActive = [Deleted].[Active],
		@DeletedArchive = [Deleted].[Archive],
		@DeletedAuditReferenceInsert = [Deleted].[AuditReferenceInsert],
		@DeletedAuditReferenceUpdate = [Deleted].[AuditReferenceUpdate],
		@DeletedDomainId = [Deleted].[DomainId],
		@DeletedId = [Deleted].[Id],	
		@InsertedActive = [Inserted].[Active],
		@InsertedArchive = [Inserted].[Archive],
		@InsertedAuditReferenceInsert = [Inserted].[AuditReferenceInsert],
		@InsertedAuditReferenceUpdate = [Inserted].[AuditReferenceUpdate]
	FROM 
		DELETED AS [Deleted]
		INNER JOIN INSERTED AS [Inserted] 
			ON [Deleted].[DomainId] = [Inserted].[DomainId]
		INNER JOIN [dbo].[AuditDatabaseStatic] AS [AuditDatabase]
			ON [Deleted].[AuditReferenceUpdate] = [AuditDatabase].[Reference]				
	SET @Error = @@ERROR
	IF (@Error <> 0) 
	BEGIN
		SET @MessageAlias = N'TOU56311'
		SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default)
		RAISERROR (@MessageText,10,1) WITH SETERROR
		RETURN
	END			
/* -------------------------------------------------------------------
	Check the session token is current and a valid account is changing
	the record. 
------------------------------------------------------------------- */
	SET @Return = NULL
	EXECUTE @Return = [dbo].[CheckAccess]
		@AccountId = @AccountId OUTPUT,
		@AuditAccessAlias = N'UPDATE',
		@AuditCategoryAlias = N'MACHINE',		
		@AuditCategoryId = @AuditCategoryId OUTPUT,
		@AuditDatabaseReference = @AuditDatabaseReference OUTPUT,
		@CurrentTimestamp = @CurrentTimestamp OUTPUT,
		@MaximumTimestamp = @MaximumTimestamp OUTPUT,
		@MessageAlias = @MessageAlias OUTPUT,
		@Reference = @InsertedAuditReferenceUpdate 
	SET @Error = @@ERROR
	IF (@Error <> 0 OR @Return IS NULL OR @Return <> 0)
	BEGIN
		SET @MessageAlias = ISNULL(LTRIM(RTRIM(@MessageAlias)),N'TOU59998')
		SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default)
		RAISERROR (@MessageText,10,1) WITH SETERROR, LOG
		RETURN
	END	
/* -------------------------------------------------------------------
	Check for the [Active] datetime being set to NULL or copied from 
	the DELETEd table and replace it with @CurrentTimestamp plus the 
	minimum supported milliseconds.   
------------------------------------------------------------------- */	
	IF (@InsertedActive IS NULL OR @InsertedActive = @DeletedActive)
		SET @InsertedActive = DATEADD(MILLISECOND, +2, @CurrentTimestamp)
/* -------------------------------------------------------------------
	This is an obscure piece of logic that makes no sense on the
	surface, it is implemented to allow a record [Archive] to be
	set to [dbo].[CurrentTimestamp]() to effectively delete the record.
		
	The InsteadOfDelete trigger can't be used for a delete because
	the new [AuditReferenceUpdate] can't be passed on the delete 
	statement, so no audit path can be maintained.
		
	To allow for latency between the call to UPDATE and the actual
	UPDATE occurring affecting the value of [dbo].[CurrentTimestamp](),
	there is a rule implemented that if INSERTED.[Archive] is 
	DELETED.[Active] plus 2 milliseconds, the request is a 
	DELETE and [Archive] will be set to [dbo].[CurrentTimestamp]().
	
	The [AuditReferenceUpdate] is left as the default value so
	the record can be undeleted again by an update with an
	[Archive] later than [dbo].[CurrentTimestamp](). 
------------------------------------------------------------------- */	
	IF (DATEADD(MILLISECOND, +2, @DeletedActive) = @InsertedArchive)
	BEGIN
		SET @InsertedArchive = DATEADD(MILLISECOND, +2, @InsertedActive)
		IF (DATEADD(MILLISECOND, -2, @InsertedActive) > @DeletedArchive AND @DeletedAuditReferenceUpdate = @AuditDatabaseReference)
		BEGIN
			SET @MessageAlias = N'TOU56312'
			SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default)
			RAISERROR (@MessageText,10,1) WITH SETERROR
			RETURN
		END
	END	
/* -------------------------------------------------------------------
	Check the [Archive] datetime being set to NULL or being  copied
	from the DELETEd table and replace it with @MaximumTimestamp.   
------------------------------------------------------------------- */			
	IF (@InsertedArchive IS NULL OR @InsertedArchive = @DeletedArchive)
		SET @InsertedArchive = @MaximumTimestamp
/* -------------------------------------------------------------------
	Check there is no inconsistant temporal overlap. 
------------------------------------------------------------------- */			
	IF (@InsertedActive < @CurrentTimestamp) 
	BEGIN
		SET @MessageAlias = N'TOU56305'
		SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default)
		RAISERROR (@MessageText,10,1) WITH SETERROR
		RETURN
	END
	IF (@InsertedArchive < @InsertedActive)
	BEGIN
		SET @MessageAlias = N'TOU56306'
		SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default)
		RAISERROR (@MessageText,10,1) WITH SETERROR
		RETURN
	END
	IF (@InsertedActive < @DeletedActive) 
	BEGIN
		SET @MessageAlias = N'TOU56313'
		SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default)
		RAISERROR (@MessageText,10,1) WITH SETERROR
		RETURN
	END	
/* -------------------------------------------------------------------
	Check the [Active] and [Archive] dates for discontiguous ranges 
	and don't enforce contiguousness if it isn't there.		 	
------------------------------------------------------------------- */			
	IF (@DeletedArchive >= @InsertedActive)
	BEGIN
		SET @DeletedArchive = DATEADD(MILLISECOND, -2, @InsertedActive)
	END
/* -------------------------------------------------------------------
	Check the new descriptor is unique across records other than the 
	currently active [Id] group. 
------------------------------------------------------------------- */	
	BEGIN
		IF EXISTS 
		(
			SELECT 
				* 
			FROM 
				[dbo].[MachineDomain] AS [MachineDomain],
				INSERTED AS [Inserted]
			WHERE
				[MachineDomain].[Id] <> @DeletedId
				AND [MachineDomain].[Name] = [Inserted].[Name]
		)
		BEGIN
			SET @MessageAlias = N'TOU56314'
			SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,N'[Name]')
			RAISERROR (@MessageText,10,1) WITH SETERROR
			RETURN		
		END			
	END	
	BEGIN
		IF EXISTS 
		(
			SELECT 
				* 
			FROM 
				[dbo].[MachineDomain] AS [MachineDomain],
				INSERTED AS [Inserted]
			WHERE
				[MachineDomain].[Id] <> @DeletedId
				AND [MachineDomain].[Serial] = [Inserted].[Serial]
		)
		BEGIN
			SET @MessageAlias = N'TOU56314'
			SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,N'[Serial]')
			RAISERROR (@MessageText,10,1) WITH SETERROR
			RETURN		
		END			
	END	
/* -------------------------------------------------------------------
	Updating a record to be a duplicate of itself is not supported 
	except when a DELETE is being executed or a deleted record is 
	being undeleted. 
------------------------------------------------------------------- */		
	IF NOT (@InsertedArchive = DATEADD(MILLISECOND, +2, @InsertedActive))
	BEGIN
		IF EXISTS 
		(
			SELECT 
				* 				
			FROM 
				DELETED AS [Deleted]
				INNER JOIN INSERTED AS [Inserted] 
					ON [Deleted].[DomainId] = [Inserted].[DomainId]	
			WHERE
				[Deleted].[AuditReferenceUpdate] = @AuditDatabaseReference 			
				AND [Deleted].[Archive] >= @CurrentTimestamp
				AND ISNULL([Deleted].[Description], 0) = ISNULL(ISNULL([Inserted].[Description], [Deleted].[Description]), 0)
				AND [Deleted].[ModelCategoryDomainId] = ISNULL([Inserted].[ModelCategoryDomainId], [Deleted].[ModelCategoryDomainId])
				AND [Deleted].[Name] = ISNULL([Inserted].[Name], [Deleted].[Name]) 
				AND ISNULL([Deleted].[Serial], 0) = ISNULL(ISNULL([Inserted].[Serial], [Deleted].[Serial]), 0)
		)
		BEGIN
			SET @MessageAlias = N'TOU56315'
			SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default)
			RAISERROR (@MessageText,10,1) WITH SETERROR
			RETURN		
		END			
	END			
/* -------------------------------------------------------------------
	UPDATE the archive timestamp for the existing record then INSERT 
	the new record.  
------------------------------------------------------------------- */			
	UPDATE [dbo].[MachineDomain]
	SET 
		[Archive] = @DeletedArchive,
		[AuditReferenceUpdate] = @InsertedAuditReferenceUpdate
	WHERE
		[DomainId] = @DeletedDomainId
	SET @Error = @@ERROR
	IF (@Error <> 0)
	BEGIN
		SET @MessageAlias = N'TOU56316'
		SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,N'[MachineId]')
		RAISERROR (@MessageText,10,1) WITH SETERROR
		RETURN		
	END		
	INSERT [dbo].[MachineDomain] 
	(
		[Active],
		[Archive],
		[ArchiveInsert],
		[AuditReferenceInsert],
		[AuditReferenceUpdate],	
		[Description],	
		[Id],
		[ModelCategoryDomainId],
		[Name],		
		[Serial]
	)
	SELECT 
		@InsertedActive,
		@InsertedArchive,
		@DeletedArchive,
		@InsertedAuditReferenceUpdate,
		@AuditDatabaseReference,
		[Description],		
		@DeletedId,
		[ModelCategoryDomainId],
		[Name],		
		[Serial]
	FROM 	
		INSERTED 
	WHERE
		[DomainId] = @DeletedDomainId
	SET @Error = @@ERROR
	SET @ReturnDomainId = SCOPE_IDENTITY()
	IF (@Error <> 0 OR @ReturnDomainId IS NULL OR @ReturnDomainId = 0)
	BEGIN
		SET @MessageAlias = N'TOU56317'
		SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,N'[MachineId]')
		RAISERROR (@MessageText,10,1) WITH SETERROR
		RETURN	
	END						
END
GO

/* -------------------------------------------------------------------
	Procedure
		SetMachine
	
	Documentation
		Project: thisProject
		Aspect: Core
		Prepared by: thisOrganisationalUnit
		For: thatOrganistionalUnit 
		
	Description
	
	Insert a record into the [Audit] table, then delete, insert or
	update a record in the [Machine] table.
   -----------------------------------------------------------------*/
CREATE PROCEDURE [SetMachine]
(
	@Active [datetime] = NULL,
	@Archive [datetime] = NULL,
	@CategoryId [int] = NULL, -- For compatibility only, use @ModelCategoryId
	@Description [nvarchar] (900) = NULL,
	@Id [int] = NULL OUTPUT,
	@ModelCategoryId [int] = NULL,
	@Name [nvarchar] (450) = NULL,
	@Serial [nvarchar] (24) = NULL,
	@Token [nchar] (36) = NULL
)
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @AccountId [int]
	DECLARE @AuditCategoryId [int]
	DECLARE @AuditDatabaseReference [uniqueidentifier]
	DECLARE @CurrentTimestamp [datetime]
	DECLARE @Error [int]
	DECLARE @MaximumTimestamp [datetime]
	DECLARE @MessageAlias [nchar] (24)	
	DECLARE @MessageObject [nvarchar] (450)	
	DECLARE @MessageText [nvarchar] (400)
	DECLARE @ParameterActive [datetime]
	DECLARE @ParameterArchive [datetime]
	DECLARE @ParameterDescription [nvarchar] (900)
	DECLARE @ParameterId [int]
	DECLARE @ParameterModelCategoryId [int]
	DECLARE @ParameterName [nvarchar] (450)
	DECLARE @ParameterSerial [nvarchar] (24)
	DECLARE @Reference [uniqueidentifier]
	DECLARE @Return [int]
	DECLARE @ThisId [int]
	DECLARE @Transaction [nchar] (36)
/* -------------------------------------------------------------------
	Get constants. 
------------------------------------------------------------------- */
	SET @MessageObject = LOWER(N'['+@@SERVERNAME+N'].['+DB_NAME()+N'].['+SESSION_USER+N'].['+OBJECT_NAME(@@PROCID)+N']')
	SET @Reference = NEWID()	
	SET @Transaction = CAST(@Reference AS [nchar] (36))
/* -------------------------------------------------------------------
	Check the session token is current and a valid account is 
	executing the procedure.
------------------------------------------------------------------- */		
	SET @Return = NULL
	EXECUTE @Return = [dbo].[CheckAccess]
		@AccountId = @AccountId OUTPUT,
		@AuditAccessAlias = N'SET',
		@AuditCategoryAlias = N'MACHINE',		
		@AuditCategoryId = @AuditCategoryId OUTPUT,
		@AuditDatabaseReference = @AuditDatabaseReference OUTPUT,
		@CurrentTimestamp = @CurrentTimestamp OUTPUT,
		@MaximumTimestamp = @MaximumTimestamp OUTPUT,
		@MessageAlias = @MessageAlias OUTPUT,
		@Token = @Token 
	SET @Error = @@ERROR
	IF (@Error <> 0 OR @Return IS NULL OR @Return <> 0)
	BEGIN
		SET @MessageAlias = ISNULL(LTRIM(RTRIM(@MessageAlias)),N'TOU59998')
		SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default)
		GOTO labelERROR
	END	
/* -------------------------------------------------------------------
	Parse the parameters. 
------------------------------------------------------------------- */
	SET @ParameterActive = @Active
	SET @ParameterArchive = ISNULL(@Archive,@MaximumTimestamp)
	SET @ParameterDescription = CASE LTRIM(RTRIM(@Description)) WHEN N'' THEN NULL ELSE LTRIM(RTRIM(@Description)) END
	SET @ParameterId = ISNULL(@Id, 0)
	SET @ParameterModelCategoryId = ISNULL(@ModelCategoryId, 0)
	IF (ISNULL(@CategoryId,0) <> 0)
	BEGIN
		SET @ParameterModelCategoryId = @CategoryId
	END	
	SET @ParameterName = CASE LTRIM(RTRIM(@Name)) WHEN N'' THEN NULL ELSE LTRIM(RTRIM(@Name)) END
/* -------------------------------------------------------------------
	Start the transaction
------------------------------------------------------------------- */	
	BEGIN TRANSACTION @Transaction
	SET @Error = @@ERROR
	IF (@Error <> 0)
	BEGIN	
		SET @MessageAlias = N'TOU56526'
		SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default)
		GOTO labelERROR
	END
	BEGIN
/* -------------------------------------------------------------------
	Insert an audit record.
------------------------------------------------------------------- */		
		INSERT [dbo].[AuditTransaction]
		(
			[AuditCategoryId],
			[Reference],
			[SessionId]
		)
		SELECT
			@AuditCategoryId,
			@Reference,
			[Id]
		FROM 
			[dbo].[Session](@CurrentTimestamp)
		WHERE
			[Reference] = CAST(@Token AS [uniqueidentifier])			
		SET @Error = @@ERROR
		SET @ThisId = @@IDENTITY
		IF (@Error <> 0 OR @ThisId IS NULL OR @ThisId = 0)
		BEGIN
			SET @MessageAlias = N'TOU56538'
			SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default)
			GOTO labelERRORINTRANSACTION
		END	
/* -------------------------------------------------------------------
	Is the request a DELETE, INSERT or UPDATE
------------------------------------------------------------------- */												
		IF (@ParameterId <> 0 AND @ParameterDescription IS NULL AND @ParameterModelCategoryId = 0 AND @ParameterName IS NULL AND @ParameterSerial IS NULL)
			GOTO labelDELETE
		IF (@ParameterId = 0 AND @ParameterModelCategoryId <> 0 AND @ParameterName IS NOT NULL AND @ParameterSerial IS NOT NULL)
			GOTO labelINSERT
		IF (@ParameterId <> 0 AND @ParameterSerial IS NULL AND (@ParameterDescription IS NOT NULL OR @ParameterModelCategoryId <> 0 OR @ParameterName IS NOT NULL))
			GOTO labelUPDATE
		BEGIN
			SET @MessageAlias = N'TOU56061'
			SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default)
			GOTO labelERRORINTRANSACTION
		END
/* -------------------------------------------------------------------
	DELETE updates the [Archive] datetime to now which effectively 
	deletes the record while maintaining temporal integrity. If the 
	@Archive parameter has been set to greater than now, it will be 
	used to allow a post-dated delete.
	
	The InsteadOfDelete trigger can't be used for a delete because the
	new [AuditReferenceUpdate] can't be passed on the delete statement, 
	so no audit path can be maintained.
	
	So there is a rule implemented that if the InsteadOfUpdate trigger 
	DELETED.[Archive] is INSERTED.[Active] plus 2 milliseconds, the 
	request is a DELETE and [Archive] will be set to now.
	
	The [AuditReferenceUpdate] is left as the default value so the 
	record can be undeleted again by an update with an [Archive] later
	than now. 					 
------------------------------------------------------------------- */							
		labelDELETE: BEGIN
			IF (@ParameterArchive IS NULL OR @ParameterArchive = @MaximumTimestamp)
			BEGIN
				SET @ParameterArchive = NULL
				SELECT 
					@ParameterArchive = DATEADD(MILLISECOND, +2, [Active]) 
				FROM 
					[dbo].[MachineDomain] 
				WHERE 
					[Id] = @ParameterId
					AND [AuditReferenceUpdate] = @AuditDatabaseReference
				SET @Error = @@ERROR
				IF (@Error <> 0)
				BEGIN
					SET @MessageAlias = N'TOU56539'
					SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default)
					GOTO labelERRORINTRANSACTION
				END
				IF (@ParameterArchive IS NULL)
				BEGIN
					SET @MessageAlias = N'TOU56063'
					SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default)
					GOTO labelERRORINTRANSACTION
				END						
			END
			UPDATE 
				[dbo].[MachineDomain]
			SET 
				[Archive] = @ParameterArchive,
				[AuditReferenceUpdate] = @Reference
			WHERE 
				[Id] = @ParameterId
			SET @Error = @@ERROR
			IF (@Error <> 0)
			BEGIN
				SET @MessageAlias = N'TOU56540'
				SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default)
				GOTO labelERRORINTRANSACTION
			END
			GOTO labelCHANGEDONE
		END
/* -------------------------------------------------------------------
	INSERT a new record
------------------------------------------------------------------- */			
		labelINSERT: BEGIN 
			INSERT [dbo].[MachineDomain]
			(
				[Active],
				[Archive],
				[AuditReferenceInsert],
				[Description],
				[ModelCategoryDomainId],
				[Name],
				[Serial]
			)
			VALUES
			(
				@ParameterActive,
				@ParameterArchive,
				@Reference,
				@ParameterDescription,
				@ParameterModelCategoryId,
				@ParameterName,
				@ParameterSerial
			)
			SET @Error = @@ERROR
			SET @ParameterId = @@IDENTITY
			IF (@Error <> 0 OR @ParameterId IS NULL OR @ParameterId = 0)
			BEGIN
				SET @MessageAlias = N'TOU56541'
				SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default)
				GOTO labelERRORINTRANSACTION
			END
			GOTO labelCHANGEDONE
		END	
/* -------------------------------------------------------------------
	UPDATE an existing record
------------------------------------------------------------------- */		
		labelUPDATE: BEGIN
			UPDATE 
				[dbo].[MachineDomain]
			SET 
				[Active] = @ParameterActive,
				[Archive] = @ParameterArchive,
				[AuditReferenceUpdate] = @Reference,
				[Description] = CASE WHEN @ParameterDescription IS NOT NULL THEN @ParameterDescription ELSE [Description] END,  				
				[ModelCategoryDomainId] = CASE WHEN @ParameterModelCategoryId <> 0 THEN @ParameterModelCategoryId ELSE [ModelCategoryDomainId] END,
				[Name] = CASE WHEN @ParameterName IS NOT NULL THEN @ParameterName ELSE [Name] END
			WHERE 
				[Id] = @ParameterId
			SET @Error = @@ERROR
			IF @Error <> 0
			BEGIN
				SET @MessageAlias = N'TOU56542'
				SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default)
				GOTO labelERRORINTRANSACTION
			END
			GOTO labelCHANGEDONE			
		END
/* -------------------------------------------------------------------
	END transaction, COMMIT the changes
------------------------------------------------------------------- */	
	END
	labelCHANGEDONE: BEGIN
		COMMIT TRANSACTION @Transaction
		SET @Error = @@ERROR
		IF @Error <> 0	
		BEGIN
			SET @MessageAlias = N'TOU56527'
			SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default)
			GOTO labelERRORINTRANSACTION
		END	
		SET @Id = @ParameterId
		RETURN 0
	END			 				
/* -------------------------------------------------------------------
	Error termination routines.  
------------------------------------------------------------------- */	
	labelERRORINTRANSACTION: BEGIN
		ROLLBACK TRANSACTION @Transaction
		GOTO labelERROR
	END	 
	labelERROR: BEGIN
		SET @Id = NULL
		RAISERROR (@MessageText,10,1)		
		RETURN CAST(RIGHT(LTRIM(RTRIM(@MessageAlias)),5) AS [int])
	END	
END
GO

/* -------------------------------------------------------------------
	Procedure
		GetMachine
	
	Documentation
		Project: thisProject
		Aspect: Core
		Prepared by: thisOrganisationalUnit
		For: thatOrganistionalUnit 
		
	Description
	
	Get a resultset from the [Machine] table.
	
	@Serial is an alias for the [Id] and is a unique serial number
	from the manufacturer. 
   -----------------------------------------------------------------*/
CREATE PROCEDURE [GetMachine]
(
	@Active [datetime] = NULL,
	@AuditId [int] = NULL,
	@Id [int] = NULL OUTPUT,
	@Name [nvarchar] (450) = NULL,
	@Serial [nvarchar] (24) = NULL,
	@Token [nchar] (36) = NULL
)
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @AccountId [int]
	DECLARE @Error [int]		
	DECLARE @Location [nvarchar] (450)
	DECLARE @MessageAlias [nchar] (24)
	DECLARE @MessageObject [nvarchar] (450)		
	DECLARE @MessageText [nvarchar] (400)
	DECLARE @ParameterActive [datetime]
	DECLARE @ParameterAuditId [int]	
	DECLARE @ParameterId [int]
	DECLARE @ParameterName [nvarchar] (450)
	DECLARE @ParameterSerial [nvarchar] (24)
	DECLARE @Return [int]
	DECLARE @RowCount [int]
/* -------------------------------------------------------------------
	Get constants.
------------------------------------------------------------------- */
	SET @MessageObject = LOWER(N'['+@@SERVERNAME+N'].['+DB_NAME()+N'].['+SESSION_USER+N'].['+OBJECT_NAME(@@PROCID)+N']')
/* -------------------------------------------------------------------
	Parse the parameters
------------------------------------------------------------------- */
	SET @ParameterActive = @Active
	SET @ParameterAuditId = @AuditId	
	SET @ParameterId = ISNULL(@Id, 0)
	SET @ParameterName = CASE LTRIM(RTRIM(@Name)) WHEN N'' THEN NULL ELSE LTRIM(RTRIM(@Name)) END	
	SET @ParameterSerial = CASE LTRIM(RTRIM(@Serial)) WHEN N'' THEN NULL ELSE LTRIM(RTRIM(@Serial)) END
/* -------------------------------------------------------------------
	Check the combination of parameters
------------------------------------------------------------------- */
	IF (@ParameterId <> 0 AND @ParameterName IS NULL AND @ParameterSerial IS NULL)
		GOTO labelOK
	IF (@ParameterId = 0 AND @ParameterName IS NOT NULL AND @ParameterSerial IS NULL)
		GOTO labelOK
	IF (@ParameterId = 0 AND @ParameterName IS NULL AND @ParameterSerial IS NOT NULL)
		GOTO labelOK	
	IF (@ParameterId = 0 AND @ParameterName IS NULL AND @ParameterSerial IS NULL)
		GOTO labelOK						
	BEGIN
		SET @MessageAlias = N'TOU56061'
		SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default)
		GOTO labelERRORMESSAGESPECIFIED
	END	
/* -------------------------------------------------------------------
	Check the session token is current and a valid account is reading
	the record. 
------------------------------------------------------------------- */	
	labelOK: SET @Return = NULL
	EXECUTE @Return = [dbo].[CheckAccess]
		@AccountId = @AccountId OUTPUT,
		@Active = @ParameterActive OUTPUT,
		@AuditAccessAlias = N'GET',
		@AuditCategoryAlias = N'MACHINE',
		@AuditId = @ParameterAuditId,
		@MessageAlias = @MessageAlias OUTPUT,
		@Token = @Token 
	SET @Error = @@ERROR
	IF (@Error <> 0 OR @Return IS NULL OR @Return <> 0)
	BEGIN
		SET @MessageAlias = ISNULL(LTRIM(RTRIM(@MessageAlias)),N'TOU59998')
		SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default)
		GOTO labelERRORMESSAGESPECIFIED
	END			
/* -------------------------------------------------------------------
	Get the resultset.
------------------------------------------------------------------- */		
	BEGIN
		SET NOCOUNT OFF
		SELECT 
			[Description],
			[Id],
			[ModelCategoryId],
			[Name],
			[Serial]
		FROM
			[dbo].[Machine](@ParameterActive)
		WHERE
			CASE @ParameterId WHEN 0 THEN 0 ELSE [Id] END = @ParameterId
			AND CASE ISNULL(@ParameterName,N'') WHEN N'' THEN N'' ELSE [Name] END = ISNULL(@ParameterName,N'') 	
			AND CASE ISNULL(@ParameterSerial,N'') WHEN N'' THEN N'' ELSE [Serial] END = ISNULL(@ParameterSerial,N'') 	
		SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT
		SET NOCOUNT ON
		IF (@Error <> 0)
		BEGIN
			SET @MessageAlias = N'TOU56521'
			SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default)
			GOTO labelERRORMESSAGESPECIFIED
		END
		IF (@RowCount = 0)
		BEGIN
			SET @MessageAlias = N'TOU56063'
			SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default)
			GOTO labelERRORMESSAGESPECIFIED
		END	
		RETURN 0		
	END
/* -------------------------------------------------------------------
	Generic error handling
------------------------------------------------------------------- */		
	labelERROR: BEGIN
		SET @MessageAlias = N'TOU59999'
		SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,@Location)
		GOTO labelERRORMESSAGESPECIFIED
	END	
	labelERRORMESSAGESPECIFIED: BEGIN
		RAISERROR (@MessageText,10,1)		
		RETURN CAST(RIGHT(LTRIM(RTRIM(@MessageAlias)),5) AS [int])
	END		
END	
GO

Jason Whitney (10/1/2009)

---

I have run into this before, and the article gives the first half of the solution. The second part is what I can't figure out; how to avoid overlapping time-frames.

The supplied solution doesn't enforce data integrity for overlapping dates. For the illustration given it may not need to, but for many applications these date ranges must be mutually exclusive. For example a patient comes in to the hospital as an outpatient (O) and has a complication and gets admitted as an inpatient (I). The data is temporal, but the dates/times cannot overlap. How would I model this?

The only solution I have been able to come up with is a new data type for interval data. (Hopefully we will see this in the next version of SQL) I tried a CLR, but I was never able to get it to work quite right. My database has failed me!

If Exists(	Select *
		From dbo.PromotedProducts As PP
			Join inserted As I
				On I.PromotionId = PP.PromotionId
            				And I.ProductId = PP.ProductId
					And I.Start <= PP.EndDate
					And I.EndDate >= PP.StartDate )
	Raiserror(...