February 9, 2011 at 9:40 am
[font="Tahoma"]
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
[/font]
February 9, 2011 at 10:06 am
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 9, 2011 at 10:32 am
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?
February 10, 2011 at 12:32 pm
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply