July 29, 2009 at 7:51 am
Dear friends
Is there a way to delay a trigger either using waitfor delay '00:00:30'. I have a trigger that i don't want it to fire straightaway. I want to delay this for 30 sec or so. I was thinking of using Waitfor delay. I am not sure how i can use it on triggers?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create trigger [dbo].[updateactivecell]
on [dbo].[productionRunHistory]
after insert
as
update productionrunaudit
set activecell=(select ph.assetid from productionrunhistory ph inner join inserted i on ph.runid=i.runid and ph.status='completed'),
where runid=
(select ph.runid from productionrunhistory ph inner join inserted i on ph.runid=i.runid and ph.status='completed')
Appreciate your help.
July 29, 2009 at 7:53 am
July 29, 2009 at 8:01 am
Thanks for the prompt response Pradeep.. Where in my trigger i can put Waitfor delay. I have used Waitfor in stored procedures but not in triggers..
It gives me an error when i try to put Waitfor in my trigger.
July 29, 2009 at 8:04 am
i'm not sure whether waitfor is not allowed in a trigger. if it's so, you can call a dummy proc from the trigger. the proc would contain the code for waitfor.
but why do u wanna do this? this will cause more blockings i guess.
July 29, 2009 at 8:06 am
While the trigger is waiting, the transaction is incomplete, and locks will be held in the table. In almost every situation I can think of, this would be a bad idea.
Why would you need a trigger to delay firing by 30 seconds?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 29, 2009 at 8:10 am
The reason i want to delay this is because the some of the fields gets updated after the audit process which is done in Java. My Java knowledge is very limited so i am not sure where to look at.
In my trigger the activecell field gets updated after a delay.
July 29, 2009 at 8:14 am
A delay in the trigger won't do what you need. It'll just hold the transaction up and probably cause the Java to time out.
Better to have the audit process handle what the trigger was going to do.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 29, 2009 at 8:17 am
What error are you getting?
Why do you need to delay execution of the trigger?
Don't forget, if you put a delay in the trigger, whatever fired the trigger is also going to be delayed. Any locks taken out by the DML statement are going to be held while your trigger is delayed, and potentially block other users.
If you are looking to execute the trigger code independently of the DML statement, take a look at Service Broker. Alternatively, you colud look into creating a separate job, and schedule it to run at various times.
July 29, 2009 at 8:21 am
sarvesh singh (7/29/2009)
The reason i want to delay this is because the some of the fields gets updated after the audit process which is done in Java. My Java knowledge is very limited so i am not sure where to look at.In my trigger the activecell field gets updated after a delay.
You can write a separate code in java or call a stored proc to update those fields separately. If some of the fields will be entered later, why delay execution of what has already been entered? As said earlier, this will hamper ur system performance badly. You can definitely update any columns later.
July 29, 2009 at 8:25 am
I've got snapshot_isolation on and read_committed_snapshot on. Will delaying the trigger still block the tables.
If it's not feasible i guess i have to look for other options, Job is one of them as Ian suggested.
July 29, 2009 at 8:31 am
sarvesh singh (7/29/2009)
I've got snapshot_isolation on and read_committed_snapshot on. Will delaying the trigger still block the tables. ...
Yes.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 29, 2009 at 8:38 am
Snapshot isolation is going to prevent someone outside of the transaction from seeing the changes.
At best as of now - you could split up the trigger, and turn it into an asynchronous process. As in - change the trigger from something that actually does the work , to something that send a message out to do whatever work the trigger happens to be doing. You can then introduce a delay that way.
Still - there are a lot of things you wouldn't be able to do this way that you can do in a trigger, and you might need to put something in to prevent logic failures (what happens if something else grabs a hold of the record between the time of the change and the tme the trigger actually fires...)
Best to follow the advice you've received so far and reengineer the front-end piece.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply