October 1, 2009 at 11:58 am
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! :crazy:
Yes, that's what I was talking about, and that is the serious problem with trying to represent an interval within a row in SQL Server.
You can do a function based check constraint that applies one of the available overlap detections, such as the simple (and SQL Server 2000 friendly). As mentioned, it's expensive. There may be better solutions for 2000, and I think there are for 2005, but I don't know them.
Essentially:
CROSS JOIN the table to itself (call them Outer and Inner)
WHERE Inner.UniqueRowKey <> Outer.UniqueRowKey (one row doesn't overlap with itself)
AND Inner.KeyThatShouldNotOverlap = Outer.KeyThatShouldNotOverlap (so we only look at row sets that, in combination, can overlap when they shouldn't)
AND Inner.effdate <= Outer.termdate (one starts before/equal to when the other ends)
AND Inner.termdate >= Outer.effdate (and that one also ends after/equal to when the other starts)
Add in as many fields in composite keys as you need.
If you don't have a truly unique row identifier... add one to the table, build one, concatenate fields to get one, or do some AND/OR within nested parenthesis as appropriate.
October 1, 2009 at 2:04 pm
Why would you want the db to do this? Why wouldn't you require the application to term the existing record with the timestamp when the change occurred -1 (millisecond, second, whatever) and start a new record with the timestamp when the change occurred to whatever the termdate is? Then the user just hits a button or whatever and the app takes care of the rest.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
October 1, 2009 at 2:19 pm
jcrawf02 (10/1/2009)
Why would you want the db to do this? Why wouldn't you require the application to..[clipped]..the app takes care of the rest.
Philosophically, with this argument, why do you want the DB to have _any_ constraints at all?
Because I assume there are multiple apps, at least one is flawed, and at least some rows will sooner or later be entered another way.
October 1, 2009 at 2:27 pm
Nadrek (10/1/2009)
jcrawf02 (10/1/2009)
Why would you want the db to do this? Why wouldn't you require the application to..[clipped]..the app takes care of the rest.Philosophically, with this argument, why do you want the DB to have _any_ constraints at all?
Because I assume there are multiple apps, at least one is flawed, and at least some rows will sooner or later be entered another way.
Hardly saying that, but you're arguing that the db needs to be perfect where the app is not, versus the reverse. Since the goal of the app should be to reduce the work the user has to perform to accomplish the task, it should already be doing this.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
October 1, 2009 at 2:41 pm
jcrawf02 (10/1/2009)
Nadrek (10/1/2009)
jcrawf02 (10/1/2009)
Why would you want the db to do this? Why wouldn't you require the application to..[clipped]..the app takes care of the rest.Philosophically, with this argument, why do you want the DB to have _any_ constraints at all?
Because I assume there are multiple apps, at least one is flawed, and at least some rows will sooner or later be entered another way.
Hardly saying that, but you're arguing that the db needs to be perfect where the app is not, versus the reverse. Since the goal of the app should be to reduce the work the user has to perform to accomplish the task, it should already be doing this.
I'm a DBA, so yes, the db must be perfect. 😀 Also we may have multiple applications accessing a single database so I don't want to rely on the application logic. Also, the application should reduce the work a user has to perform, but this is completely different from accurately modeling the data. Part of data modeling in this case is the ability to store interval or ranged data (dates or numbers) that do not overlap.
October 1, 2009 at 2:59 pm
As a developer, the best practice here in my opinion, is to wrap all data update/inserts/deletes for those table into a stored procedure with the validation logic. All apps would have no ability to update that data without calling the stored procedure to proxy the work. I know this may not help for existing legacy apps where source code isn't available and recompiling (if it's a compiled app) isn't possible, but the more business rules that can go on the server and not on the client, the better we are.
--Jim
October 1, 2009 at 3:18 pm
James Stephens (10/1/2009)
As a developer, the best practice here in my opinion, is to wrap all data update/inserts/deletes for those table into a stored procedure with the validation logic. All apps would have no ability to update that data without calling the stored procedure to proxy the work. I know this may not help for existing legacy apps where source code isn't available and recompiling (if it's a compiled app) isn't possible, but the more business rules that can go on the server and not on the client, the better we are.--Jim
That's a great approach to strive for, but unless insert, update, and delete are revoked on the table, somebody might still connect outside of your application and enter data directly into it that violates business rules. Also, this approach does not eliminate any overhead, it just moves it to a different place.
On the bright side, if the rules are enforced from insert/update triggers, the entire table does not need to cross join to itself, it just needs to cross join with the "inserted" temp table that is provided to the trigger by the database engine. Hopefully only a small number of rows (if more than one) will ever be inserted/updated at a time during normal production activity.
October 1, 2009 at 5:48 pm
This will make my job obsolete as a BI person but ...
Add an identity column (surrogate key), and a Change_Type (price change, promo etc) to the renamed Prod_Hist (was called Promo_Prod) along with the natural keys (only really need the from date as these must be continuous from and to dates - more than one promo active does not make sense to me - sounds like a larger thing than product - we call it a bundle in telco - thats another discussion).
Use the TSQL Merge Statement as follows ...
For new (inserted) products you create a new Promo_Prod with no to date or largest date possible for the data type (means no expiry) and the current price. For changes to product price use merge to update old price's to date and insert new price from date with same to date and null for the to date. Same for promos changes. When orders are created they use the price in the Prod-Hist since it only exists there. There's a lot more to this for complex requirements but this is a good taste for a simple requirement around price and promo changes.
excuse my quick answer for spelling, clarity etc.
October 2, 2009 at 3:52 am
Jason Whitney (10/1/2009)
jcrawf02 (10/1/2009)
Nadrek (10/1/2009)
jcrawf02 (10/1/2009)
Why would you want the db to do this? Why wouldn't you require the application to..[clipped]..the app takes care of the rest.I'm a DBA, so yes, the db must be perfect. 😀 Also we may have multiple applications accessing a single database so I don't want to rely on the application logic.
That's what stored procedures are for.
October 2, 2009 at 4:37 am
Quite - as the DBA you should be revoking all privileges on all tables and only allowing pre designed updates via stored procedures - that's the only way you can guarantee data integrity. Are your developers DBAs? No. So why give them the opportunity to compromise your data? Yeah, yeah, rapid response yadda yadda, flexibility yadda yadda, all means nothing when your data is compromised.
October 2, 2009 at 6:25 am
Richard Gardner-291039 (10/2/2009)
Quite - as the DBA you should be revoking all privileges on all tables and only allowing pre designed updates via stored procedures - that's the only way you can guarantee data integrity. Are your developers DBAs? No. So why give them the opportunity to compromise your data? Yeah, yeah, rapid response yadda yadda, flexibility yadda yadda, all means nothing when your data is compromised.
No, the only way you can get close to guaranteeing data integrity is to implement database constraints that, when active, actively prevent data which lacks integrity from being written to the database.
Further, there is a huge difference in most actual businesses between whatever that businesses's security ideal is, and the security reality there. There is also a large degree of variation on what the security ideal is. Regardless of the desired state, the actual state in many places is different; even if you do and can locking SQL writing access down to DBA's doesn't guarantee that a DBA won't make a mistake at some point. All DBA's are humans, no humans are perfect, therefore, no DBA's are perfect.
A function based CHECK constraint to verify that date ranges that don't overlap is philosophically not significantly different than choosing to use NUMERIC(9) instead of CHAR(9) for 9 digit numbers, or to have a CHECK constraint limiting an indicator field to only the four possible valid values. Yes, it's more expensive, but all methods I've seen are expensive; I prefer the data integrity method that is:
A) Most likely to functional at any given point in time (I see triggers disabled more often than constraints, and I see stored procedures bypassed more often than I see triggers disabled)
B) More easily checked in a generic fashion (DBCC CHECKCONSTRAINTS)
C) Capable of preventing issues in the widest range of data write methods (write methods include but are not limited to stored procedures, triggers, ad-hoc SQL from applications, hand-written ad-hoc SQL, bcp/BULK INSERT, and so on).
D) Closest to "guaranteeing" that the bad data does not currently exist, even if it existed prior to the most recent enabling/updating of the prevention mechanism.
I've often seen instances of bad data, and when I ask someone else, the answer is "Oh! That shouldn't happen." Occasionally, the answer is "That can't happen," which is clearly false as it did happen. Database level constraints are the best way I know of to limit issues from happening; whether they're function based and expensive, whether they're an IN ('M','F','U') check, or whether they're choosing NUMERIC(9) or another datatype instead of CHAR(9).
October 2, 2009 at 8:28 am
This was an excellently written article. It makes a point to both technical and non-technical people. I have read the replies and have to say, as with cats there are many ways to skin them. I would not use the design as presented, yet I understand the presenter was trying to get a specific point across about temporal database design and not how to effective design database entity keying relationhsips.
We have “Lunch-N-Learns” at my organization and I am going to use this article as the basis of one of those sessions… with required attendance (we provide the lunch when attendance is required). Everyone in a data-centric organization should be aware of these principles, whether they are techies or not.
Thank you Sam and I look forward to your next article.
October 2, 2009 at 3:29 pm
I have to mention that seeing the title of this article made me wonder if SSC had changed into a different type of website overnight.
I agree with that. I thought I was going to witness the "softer" side of technology. Effective Dating could be important considering the long hours many of us have to work. I can date, but, doing it effectively takes additional skills. I was especially interested in the date recovery plan or the date backup plan. Can I restore the date back to the point before I answered the question, "Do these pants make me look fat?" :hehe:
October 2, 2009 at 8:38 pm
Nadrek (10/2/2009)
[Database level constraints are the best way I know of to limit issues from happening; whether they're function based and expensive [...]
Agreed, and there have been comments woven through the thread so far, that effective dates are expensive, ditto for any constraint that is difficult to suborn.
If commercial reality, or in other words, keeping your job, means taking shortcuts, then it's up to the DBA to recommend spending the immediately available budget on the lower total cost constraints and document the exposure involved in not implementing the higher total cost constraints.
It's then up to the CxO's to determine the total cost to the business of not implementing the higher total cost constraints, and carry the responsibility for the risk.
If you have no authority for the budget, you have no responsibility for those extraneous costs, after you've made the recommendations that fall within generally agreed best practice.
But then "generally agreed best practice" is a whole other can of worms.
Back on to topic, here's some fragments that I exhumed from the archive of one of my succesful SQL2000 projects. Yes, I have had my share of unsuccessful projects. Doh.
As I don't write nearly as well, nor as clearly, as Sam Bendayan, this response from here on, isn't pitched to those fairly new to the industry, only to those that won't hesitate to tear me to shreds, eh REBAR.
The gist of this project is that a "record" is represented by a [DomainId], the temporally unique aspect of the record is represented by the [Id], within the [DomainId]. There were a huge number of auditability constraints placed on the project. In order to work with the auditability and the outrageous expense of temporal consistancy at any -specified- point in time, not just now, the logic was based on shortest path for most frequently used requests, hence the exception logic may be drawn out and regardless of that, a -substantial- amount of horsepower was in use to run the production systems.
The five fragments are for a CREATE TABLE, the INSTEAD OF INSERT trigger, the INSTEAD OF UPDATE trigger, a SET procedure to do INSERT, UPDATE and DELETE, and a GET scalar procedure. These should give a pretty good overview of how the temporal management aspects of the project came together, without worrying about the auditability and reporting, which moved out of SQL and into web services and proprietary products.
Horses, for courses, as long as -all- the data is there, and the integrity is ensured, I have no problem with positively bizarre business rules being implemented for RO off the database and on the application servers.
The project database stored batched transactional data from various revenue generating machines, a CRM structure for multiple companies owning subsets of the machines, which were occasionally sold between organisations (no, no, no, I did not want to do this), and batched OLAP denormalisations where the DBMS simply could not cope with a report structure across the temporal and organisational structure.
Some of what I did with this project, I would love to redo with SQL2008, particularly using the advances in DATE and TIME handling and using CLR, although I still have a bad feeling about some of the performance issues in that area.
As this code was dug off CD and isn't recent production code, any errors are entirely mine, incurred during coding, and I should be lambasted for them.
/* -------------------------------------------------------------------
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,
@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
Peter Edmunds ex-Geek
October 6, 2009 at 12:44 pm
You can simultaneously hold prices for List, Promotional, Distributor, Partner, & your cousin’s wife’s Aunt Billy. Just look at promotions as just another form of preferential pricing.
Split pricing into three elements: a price list header, price list detail and parts.
•Parts (SalableParts below) do not need to have an embedded price; they need to have an associated price.
•PriceLists is just what it says – the name and effective dates of a list of parts prices. End Users can give them a meaningful business name, you don’t care. And you can have upwards of a zillion of them (technically speaking) that can co-exist as the business needs.
•All PriceListParts does is associate a SalableParts record and a PriceLists record and carry a price and effective date range with a price.
Now, parts can be created by Bubba without any pricing decision, Pricelists can be managed by Daryll without any parts, and when everything is in place multiple prices can be assigned Sue to multiple pricelists for multiple parts.
For the business:
•A promotional campaign for a single part can change the price over time
•A promotional campaign can have different parts at different times
•Non-promotional pricing for Distributors & your cousin’s wife’s Aunt Billy are a gimme.
•You can easily print the List price, preferential price, & how much the buyer saved by buying from you on the invoice.
Caution:
Before you implement any effective date/time series structures, the business MUST decide how often changes can be made. What is the smallest amount of time an item can priced? One day, one minute, and one year requires different implementation.
The only time a PriceListParts record can be used is when the IsActive flag is true for the PriceListParts, and both associated SalableParts and PriceLists records. Since I am lazy, I created IsActive as a computed column based on function NowInInclusiveDateRange.
Ensure that for a given price list/part association in PriceListParts that there are no date range overlaps. Otherwise, your cousin’s wife’s Aunt Billy will have multiple prices in effect and available at the same time for blue turnip spoon tweezers.
Be sure to carefully consider the meaning of “changing a price” and what pricing history the business wants to maintain. Is it changing a current PriceListParts record due to incorrect entry, or is it actually adding a new PriceListParts record because there is a different price for a different span of time. Users will attempt to edit an existing record because it is easier, and the record of the prior price will have been destroyed
Structurally:
PriceLists 1 to n PriceListParts n to 1 SalableParts
create function [dbo].[NowInInclusiveDateRange]
(
@FromDate datetime,
@NowDate datetime,
@ToDate datetime
)
returns bit
as
begin
if @FromDate is null return 0
if @NowDate is null return 0
if @ToDate is null return 0
if @UseNow < @FromDate return 0
if @UseNow > @ToDate return 0
return 1
end
GO
CREATE TABLE [dbo].[PriceLists](
[PriceListId] [int] IDENTITY(1,1) NOT NULL,
[IsActive] AS ([dbo].[NowInInclusiveDayDateRange]([ActiveDate], getdate(), [InactiveDate])),
[ActiveDate] [datetime] NOT NULL,
[InactiveDate] [datetime] NOT NULL,
[PriceListName] [varchar](50) NOT NULL,
[Note] [varchar](1600) NULL,
[AddUTC] [datetime] NULL DEFAULT (getutcdate()),
[AddBy] [varchar](50) NOT NULL,
[ChangeUTC] [datetime] NULL,
[ChangeBy] [varchar](50) NULL,
CONSTRAINT [PK__PriceLists] PRIMARY KEY CLUSTERED
(
[PriceListId] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PriceListParts](
[PriceListPartId] [int] IDENTITY(1,1) NOT NULL,
[PartNum] [varchar](15) NOT NULL,
[PriceListId] [int] NOT NULL,
[IsActive] AS ([dbo].[NowInInclusiveDayDateRange]([ActiveDate], getdate(), [InactiveDate])),
[UnitPrice] [money] NULL DEFAULT (0),
[ActiveDate] [datetime] NOT NULL,
[InactiveDate] [datetime] NOT NULL,
[Note] [varchar](4000) NULL,
[AddUTC] [datetime] NULL DEFAULT (getutcdate()),
[AddBy] [varchar](50) NOT NULL,
[ChangeUTC] [datetime] NULL,
[ChangeBy] [varchar](50) NULL,
CONSTRAINT [PK__PriceListParts] PRIMARY KEY CLUSTERED
(
[PriceListPartId] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[SalableParts](
[PartNum] [varchar](15) NOT NULL,
[IsActive] AS ([dbo].[NowInInclusiveDayDateRange]([ActiveDate], getdate(), [InactiveDate])),
[ActiveDate] [datetime] NOT NULL,
[InactiveDate] [datetime] NULL DEFAULT (dateadd(year, 1, getdate())),
[UnitOfSale] [varchar](10) NOT NULL,
[Descrip] [varchar](400) NOT NULL,
[Note] [varchar](4000) NULL,
[AddUTC] [datetime] NULL DEFAULT (getutcdate()),
[AddBy] [varchar](50) NOT NULL,
[ChangeUTC] [datetime] NULL,
[ChangeBy] [varchar](50) NULL,
CONSTRAINT [PK__SalableParts] PRIMARY KEY CLUSTERED
(
[PartNum] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PriceListParts] WITH NOCHECK ADD CONSTRAINT [FK_PriceListParts_PriceLists] FOREIGN KEY([PriceListId])
REFERENCES [dbo].[PriceLists] ([PriceListId])
GO
ALTER TABLE [dbo].[PriceListParts] CHECK CONSTRAINT [FK_PriceListParts_PriceLists]
GO
ALTER TABLE [dbo].[PriceListParts] WITH NOCHECK ADD CONSTRAINT [FK_PriceListParts_SalableParts] FOREIGN KEY([PartNum])
REFERENCES [dbo].[SalableParts] ([PartNum])
GO
ALTER TABLE [dbo].[PriceListParts] CHECK CONSTRAINT [FK_PriceListParts_SalableParts]
GO
Viewing 15 posts - 31 through 45 (of 46 total)
You must be logged in to reply to this topic. Login to reply