SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Issues with Update DateTime column with GETDATE() using insert and delete triggers


Issues with Update DateTime column with GETDATE() using insert and delete triggers

Author
Message
aksheela
aksheela
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 54

Hi All,

I have some issues while updating the DateTime column with GetDate() using Insert and Delete Triggers.

I have Insert and Delete triggers on table t1, if there is any insert or delete operation occurs then I have to update another table t2 with current DateTime. For one record it's working fine but if I need to insert and delete multiple records from table t1, I am getting different DateTimes in table t2.

Trigger Definition:

CREATE TRIGGER [t1_Insert_Delete]
ON [dbo].[t1] FOR INSERT, DELETE
AS

DECLARE @dt DATETIME
SET @dt = GETDATE()

-- insert
INSERT t2 (ID, RecChangedOn)
SELECT ID, @dt FROM inserted

-- delete
INSERT t2 (ID, RecChangedOn)
SELECT ID @dt FROM deleted


If I have multiple inserts and deletes on the table t1, my table t2 looks like this.

ID RecChangedOn
1 2011-02-09 10:54:16.227
2 2011-02-09 10:54:16.230
3 2011-02-09 10:54:16.233

Here the problem is I need to treat all these 3 datetimes as single datetime. How can I acheive that?

Below 2 solutions doesn't work. Sad

1) If I truncate the milliseconds part from the datetime column, I am having some issues in certain situations like below.

ID RecChangedOn
1 2011-02-09 10:54:16.994 this becomes 2011-02-09 10:54:16 (OK)
2 2011-02-09 10:54:16.997 this becomes 2011-02-09 10:54:16 (OK)
3 2011-02-09 10:54:17.000 this becomes 2011-02-09 10:54:17 (Not OK)

2) If I round the milliseconds part from the datetime column, I am having some issues in certain situations like below.

ID RecChangedOn
1 2011-02-09 10:54:16.497 this becomes 2011-02-09 10:54:16 (OK)
2 2011-02-09 10:54:16.500 this becomes 2011-02-09 10:54:17 (Not OK)
3 2011-02-09 10:54:16.503 this becomes 2011-02-09 10:54:17 (Not OK)

Please help me out, what to do in these situations. Thanks in advance.

FYI ... currently I am using SQL Server 2005.

Thanks,
aks


Evil Kraig F
Evil Kraig F
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39345 Visits: 7660
Aksheela,

Nice description of the problem and the code, thanks. However, the one thing you left out was the delete statement, so I have a few questions.

Do your deletes occur simultaneously in one statement? IE: DELETE FROM tbl1 WHERE colA = @Param, or do you send multiple delete statements from the front end?

If it's multiple delete statements, such as from an ADO recordset doing dynamic SQL, there's nothing you can do to fix this, they are multiple statements, thus firing multiple triggers.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
aksheela
aksheela
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 54
Yes you are right. I missed that part.

I am doing multiples deletes at a time using the front end of the application that is through ADO.Net recordset.

Do you think any other alternate solution to do this?
Evil Kraig F
Evil Kraig F
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39345 Visits: 7660
aksheela (2/9/2011)
Yes you are right. I missed that part.

I am doing multiples deletes at a time using the front end of the application that is through ADO.Net recordset.

Do you think any other alternate solution to do this?


Sorry, lost track of the thread in the shuffle yesterday.

So, you are getting multiple delete statements sent down. There's no way to handle this, then, via trigger. The trigger has no idea what you're trying to attempt and its mechanics don't behave that way. The only workaround I could think of is setting the audit parameter manually from ADO and forcing it to send the same time on its 'batch'.

Even that's not really a good solution. What you really want it to do is have the ADO call a proc that does a mass delete and let the timestamp and trigger behave properly. All the items will end up with the same timestamp since they enter the trigger simultaneously in the deleted table.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search