Trigger on INSERT datetime values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Trigger on INSERT datetime values

Author

Topic

preben jensen
Starting Member

7 Posts

Posted - 2007-11-02 : 11:30:49

Hi.
I would like to create a trigger on insert, update for an table like this:

MyTable (myDate datetime, myTime datetime)

Now! to ease my sql statments (and performance reson) I want to make sure that the collum myDate always have the timepart set to '00:00:00' and the myTime collum alwaysshave the datepart set to
'1/1/1900'

Can any one please help.
thx

Preben Jensen

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-11-02 : 11:45:02

I'm suspicious that your approach is a good solution but to answer your question, here is one way to convert "getdate()" to 2 seperate values as you specified:


select	dateadd(day, datediff(day, 0, getdate()), 0)
	,dateadd(day, datediff(day, 0, getdate()) * -1, getdate())

Be One with the Optimizer
TG

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-02 : 11:45:11

here are the converts, but not sure if you want to use a trigger. you may want to create column constraints...

ALTER TABLE dbo.MyTable ADD CONSTRAINT
CK_MyTable_myDate CHECK (myDate = CONVERT(DATETIME, CONVERT(char(10), myDate, 121)))
GO
ALTER TABLE dbo.MyTable ADD CONSTRAINT
CK_MyTable_myTime CHECK (myTime = CONVERT(DATETIME, '1900-01-01 ' + CONVERT(char(8), myTime, 108)))
GO

preben jensen
Starting Member

7 Posts

Posted - 2007-11-02 : 12:56:21

Ok I understand the constraint approach. But what if I dont want to loose the entry?
That is why I want to force a convert an proceede the insert or update. This is very important date, and otheres developers might not apply to my domain rules.

But what is your suggestion

Preben Jensen

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-02 : 13:09:52

my mistake, your goal was not to simply constrain but also change data to match the constraint. you would have to use a trigger in that case. if ALL data entry and modification occurred through stored procedures, then converting there would have been an option for you as well.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-11-02 : 13:23:16

However you go, one thing to consider for performance reasons is try to avoid converting out and back into datetime.

Is your input a single datetime value or is the data starting out as seperate date and time values? If seperate, what are the datatypes?

I have heard some discussions about storing a date in different columns like you are suggesting but the typical method is to store a single DATETIME value and using sql server's many datetime functions to perform your business logic. If you don't mind going into it I am curious why you want to do it that way.

Be One with the Optimizer
TG

preben jensen
Starting Member

7 Posts

Posted - 2007-11-02 : 13:45:50

The table is going to have a lot of rows, its going to grow and grow and grow.
And I have select different ranges of dates (user demands).
So lets say that the result set hav 50.000 rows out of 1.000.000
then the testing in the WHERE caluse must be faster if its only have to evaluate on the datepart.
as in sql: WHERE myDate < @param.
instead of haveing som Date function in the where clause.

That is why I want to do the date function on insert, update. at this time the user will not suffer much. but in the SELECT statement the user will suffer a lot, if the query is not fast.

But please reply. I realy want to do this right.

Preben Jensen

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-02 : 14:35:12

if the only reason you are splitting the date and time is for select performance using a search argument, you should keep them together. change the where to something like this...

WHERE myDate BETWEEN COVNERT(char(10), @parm, 121) AND COVNERT(char(10), @parm, 121) + ' 23:59:59.997'

then start looking at indexing options to further speed up the query

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-11-02 : 14:59:09

Preben,

Having a function the WHERE clause is not a big deal if done corrrectly. Here are some examples that show how to do it, plus you are keeping you are not using two columns (which presumably would use more space and cause more IO to read it all in):

DECLARE @MyTable TABLE(ID INT, MyDate DATETIME)

INSERT @MyTable
SELECT 1, CURRENT_TIMESTAMP - 1
UNION ALL SELECT 2, CURRENT_TIMESTAMP + 5
UNION ALL SELECT 3, CURRENT_TIMESTAMP - 5
UNION ALL SELECT 4, CURRENT_TIMESTAMP - 10
UNION ALL SELECT 5, CURRENT_TIMESTAMP - 15
UNION ALL SELECT 6, CURRENT_TIMESTAMP - 25
UNION ALL SELECT 7, CURRENT_TIMESTAMP - 35
UNION ALL SELECT 8, CURRENT_TIMESTAMP - 45
UNION ALL SELECT 9, CURRENT_TIMESTAMP - 50
UNION ALL SELECT 10, CURRENT_TIMESTAMP - 60

SELECT *
FROM @MyTable

-- Get records for this month
SELECT *
FROM @MyTable
WHERE 
	MyDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0)
	AND MyDate < DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP) + 1, 0)

-- Get records for last month
SELECT *
FROM @MyTable
WHERE 
	MyDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP) - 1, 0)
	AND MyDate < DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0)

preben jensen
Starting Member

7 Posts

Posted - 2007-11-02 : 15:05:31

I am very sure that having convert functions in the where clause it's going to slow thing down.
then for each row its has to do convert to evaluate. There must be a better way. as I said there is going to a lot of row

Preben Jensen

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-02 : 15:16:20

a convert on a column would have a serious performance impact. the case you have above is with an argument, and convert on the argument is negligible

preben jensen
Starting Member

7 Posts

Posted - 2007-11-02 : 16:03:13

I dont want to convert on the entire column on insert, only on the inserted row's column [myDate]
then there is only one call to the convert functions.
somthing like:

// The Table
TABLE(ID INT, MyDate DATETIME)

// Trigger
CREATE TRIGGER CheckMyDate
ON MyTable
AFTER INSERT
AS
BEGIN

DECLARE @tempDate DATETIME

SET @tempDate =(SELECT myDate FROM inserted)

UPDATE MyTable
SET myDate = // help needed: create an new DATETIME from @tempDate with the timepart set to 00:00:00 //
WHERE ID = inserted.ID

END

Ok I know that it will slow the INSERT statement down,
but it will be mutch faster to query.
(I think)

Preben Jensen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-02 : 16:14:16

quote:
Originally posted by preben jensen

UPDATE MyTable
SET myDate = // help needed: create an new DATETIME from @tempDate with the timepart set to 00:00:00 //
WHERE ID = inserted.ID

SET myDate = DATEADD(Day, DATEDIFF(Day, 0, @tempDate), 0)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

preben jensen
Starting Member

7 Posts

Posted - 2007-11-02 : 16:14:26

Anyway please help me create the DATETIME, Then i will do some testing.
I promise to post the result. Its going to be interesting :)

Preben Jensen

preben jensen
Starting Member

7 Posts

Posted - 2007-11-02 : 16:21:27

thx tkizer.
but do you think my approch is good or ?

Preben Jensen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-03 : 02:21:11

Replace

DECLARE @tempDate DATETIME

SET @tempDate =(SELECT myDate FROM inserted)

UPDATE MyTable
SET myDate = // help needed: create an new DATETIME from @tempDate with the timepart set to 00:00:00 //
WHERE ID = inserted.ID

by

UPDATE M
SET M.myDate = dateadd(day,datediff(day,0,I.myDate),0)
from MyTable M inner join Inserted I
on M.ID = I.ID

PS

But I would handle date and time part in query itself and not to use trigger

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources