Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Issues with Update DateTime column with GETDATE() using insert and delete triggers Expand / Collapse
Author
Message
Posted Wednesday, February 9, 2011 9:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 10, 2011 1:34 PM
Points: 2, 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.

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

Post #1061302
Posted Wednesday, February 9, 2011 10:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:51 AM
Points: 5,446, Visits: 7,616
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
Post #1061312
Posted Wednesday, February 9, 2011 10:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 10, 2011 1:34 PM
Points: 2, 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?
Post #1061341
Posted Thursday, February 10, 2011 12:32 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:51 AM
Points: 5,446, Visits: 7,616
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
Post #1062247
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse