Timed Triggers.. Possible ???

Question

Post reply

Timed Triggers.. Possible ???

venki.ffcl

Old Hand

Points: 326
More actions
April 4, 2009 at 12:44 am

#136398

Can any one please explain
" a procedure should be executed in a particular time using a trigger ..."
Is it possible ?
Thanks in advance

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Interview question?

Triggers fire when a change happens to a table. That's it. If you want scheduling, try SQL Agent.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

Heh... I agree... probably an inteview question.

I believe the question actually asks "how would you write a trigger that executed another stored procedure but only at certain times of the day?"

I'd probably make a table with start and end times with a "0" element to form ranges of times and test to see if GETDATE() (with the date element modified to "0") fell into one of the ranges. If it did, execute the proc.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

Jeff Moden (4/4/2009)
Heh... I agree... probably an inteview question.
I believe the question actually asks "how would you write a trigger that executed another stored procedure but only at certain times of the day?"
I'd probably make a table with start and end times with a "0" element to form ranges of times and test to see if GETDATE() (with the date element modified to "0") fell into one of the ranges. If it did, execute the proc.

I wouldn't do it in a trigger. I'd use a job like Gail mentioned.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

Jack Corbett (4/6/2009)
Jeff Moden (4/4/2009)
Heh... I agree... probably an inteview question.
I believe the question actually asks "how would you write a trigger that executed another stored procedure but only at certain times of the day?"
I'd probably make a table with start and end times with a "0" element to form ranges of times and test to see if GETDATE() (with the date element modified to "0") fell into one of the ranges. If it did, execute the proc.
I wouldn't do it in a trigger. I'd use a job like Gail mentioned.

You'll get no argument from me there. However, if it was an interview question and even if you mention that fine alternative, they may still want to know the answer as part of the interview. To wit, if they insist that doing such a thing in the trigger is the best thing, you may actually want to find a different place to work for... some place where they understand that this type of thing could, in fact, be a huge problem depending on what's being done.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GSquared SSC Guru Points: 260824 More actions · Answer 5

If you had a situation where data updates (insert/update/delete) were supposed to have a specific result if done at a certain time, then a trigger with a "run at this time" check might be appropriate. Might not be something that calls for a job.

Imagine a scenario where any orders placed after 5 PM need to be handled differently than ones placed before 5 PM. Wouldn't do that with a job. Would be better to handle that in the insert proc, but could certainly be done with a trigger too.

So it could be a legit business requirement.

If it was a set of specific times, I'd definitely build a table for it, like Jeff. Easier to manage than hard-coding the times into the proc/trigger.

- 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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 6

I agree with the facts that it could be a legit business requirement and that using a table to store the times would be the way to go.

Some would even argue that a proc isn't the place for it either, but in the business layer of the application.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

GSquared SSC Guru Points: 260824 More actions · Answer 7

Jack Corbett (4/6/2009)
I agree with the facts that it could be a legit business requirement and that using a table to store the times would be the way to go.
Some would even argue that a proc isn't the place for it either, but in the business layer of the application.

Yeah, I thought of that too. But, if someone's asking me how I would do it, it'll be a proc/trigger, because if anyone is asking me how to implement stuff in the (presumably .NET) business layer, it's not going to be very productive. 🙂

Interviewer: How would you implement rule X in the business layer of an application, outside the database?

Me: Ask one of the .NET devs to do it.

- 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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 8

GSquared (4/6/2009)
Jack Corbett (4/6/2009)
I agree with the facts that it could be a legit business requirement and that using a table to store the times would be the way to go.
Some would even argue that a proc isn't the place for it either, but in the business layer of the application.
Yeah, I thought of that too. But, if someone's asking me how I would do it, it'll be a proc/trigger, because if anyone is asking me how to implement stuff in the (presumably .NET) business layer, it's not going to be very productive. 🙂
Interviewer: How would you implement rule X in the business layer of an application, outside the database?
Me: Ask one of the .NET devs to do it.

Good answer. Would be mine, too.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Vijaya Kadiyala SSCrazy Eights Points: 9149 More actions · Answer 9

venki.ffcl (4/4/2009)
Can any one please explain
" a procedure should be executed in a particular time using a trigger ..."

Can you tell me why do you want to use trigger to do this?? I hope i am not asking another interview question:hehe:

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers