October 14, 2008 at 11:00 am
Hi,
I have a date_created column of smalldatetime type. I need to enforce that I'll have only one entry per day in the table using this column and I need this to take place at the db/table level, not application level.
Any ideas?
Thanks.
October 14, 2008 at 11:15 am
I am not sure, but I think that you can do this with an INSTEAD OF trigger. Put a Unique Key on the field and then in the trigger, remove the Time part just leaving the Date part.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 14, 2008 at 11:42 am
That could work, I see only one problem: if I have one transaction with multiple inserts, how is this going to behave?
To my understanding, there's no handling in triggers using "for each row" like in Oracle.
Thx.
October 14, 2008 at 11:53 am
Daniel C (10/14/2008)
That could work, I see only one problem: if I have one transaction with multiple inserts, how is this going to behave?To my understanding, there's no handling in triggers using "for each row" like in Oracle.
Thx.
You're right: SQL Triggers are per-statement, not per-row. However, the good news is (1) this is much faster and (2) you do not need per-row (certainly not in this case):
CREATE TRIGGER InsteadTrigger on YourTable
INSTEAD OF INSERT AS
BEGIN
INSERT INTO YourTable(date_created)
SELECT DATEADD(day, 0, (DATEDIFF(day, 0, date_created)))
FROM inserted
END
Note how this fixes them all at once.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 14, 2008 at 1:22 pm
What you're saying over there doesn't work at all for the intended purpose - smalldatetime/datetime/bigdatetime uniqueness enforcement.
Yes, indeed, I have the unique index on date_created (smalldatetime NOT NULL) and indeed the trigger removes the time part and inserts only the date. BUT, given the 1 minute precision of the smalldatetime datatype, two inserts with the same date fired within 1 min and 1 s apart or more will be successfully executed due to the fact they're perceived as two different values. So it defeats the whole purpose.
As a side note, it would have been useful for MS to provide in SQL Server 2005 a date data type. Like any other normal decent database - ahem - Oracle. Even that pos called MySQL has a date data type.
Still looking for a solution on this.
October 14, 2008 at 1:44 pm
Daniel C (10/14/2008)
Yes, indeed, I have the unique index on date_created (smalldatetime NOT NULL) and indeed the trigger removes the time part and inserts only the date. BUT, given the 1 minute precision of the smalldatetime datatype, two inserts with the same date fired within 1 min and 1 s apart or more will be successfully executed due to the fact they're perceived as two different values.
Um no, that's not how it works. In both cases the trigger will trim off the hours and minutes before the record gets stored. Since at that point they will have duplicate values (the same date, with no time) they will be rejected by the unique key.
As a side note, it would have been useful for MS to provide in SQL Server 2005 a date data type.
This have been provided in SQL Server 2008.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 14, 2008 at 1:49 pm
Daniel C (10/14/2008)
What you're saying over there doesn't work at all for the intended purpose - smalldatetime/datetime/bigdatetime uniqueness enforcement.Still looking for a solution on this.
Did you try it?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 14, 2008 at 2:02 pm
I just tested your whole scenario and it turns out that it doesn't quite work as you expected.
I have the table:
CREATE TABLE [dbo].[testumasii](
[id] [int] IDENTITY(1,1) NOT NULL,
[date_created] [smalldatetime] NOT NULL,
CONSTRAINT [PK_testumasii] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
I have the trigger:
DROP TRIGGER DateCreatedInsteadTrigger
GO
CREATE TRIGGER DateCreatedInsteadTrigger on testumasii
INSTEAD OF INSERT AS
BEGIN
INSERT INTO testumasii(date_created)
SELECT DATEADD(day, 0, (DATEDIFF(day, 0, date_created)))
FROM inserted
END
...and I'm running repeatedly this:
INSERT INTO testumasii VALUES ( getdate());
select * from testumasii;
The result is:
12008-10-14 00:00:00
22008-10-14 00:00:00
32008-10-14 00:00:00
42008-10-14 00:00:00
52008-10-14 00:00:00
62008-10-14 00:00:00
72008-10-14 00:00:00
82008-10-14 00:00:00
SO it doesn't work as you stated. Let me know if I'm doing something wrong and I'll correct my scenario and logic. Thanks.
October 14, 2008 at 2:05 pm
Oops, now it's really working IF I try to add the unique constraint on date_created, as well. That's much better.
October 14, 2008 at 2:14 pm
Glad it worked out for you. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 14, 2008 at 2:35 pm
Yup, everything turned out fine.
Thanks a bunch for the support.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply