By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,163 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

Trigger in a certain period of time of day

P: 25
Hi all,

I have two tables A(a1,a2,a3), B(b1,b2,b3) and a period of time delta t [t1,t2].

When it is between t1 - t2 of time, if there is a row inserted in table A, the field b1 of table B will be updated such as b1=b1+1.

Moreover, I also have a web application which can assign value for t1, t2 through an aspx page.

Could you tell me how to do that?

Thank kiu so much
Dec 8 '09 #1

✓ answered by ck9663

You have to create a trigger in UpdateInfo. You can either:

1. Create an INSTEAD OF trigger, check the system date when it fires then issue a return if it's outside of the range you need.
2. Create a AFTER trigger, use TRANSACTION, check the system date and issue a ROLLBACK then RETURN if it's outside of range. If it's within the range, proceed with the trigger and issue a COMMIT at the end before issuing a RETURN.

Happy Coding!!!

--- CK

Share this Question
Share on Google+
7 Replies


debasisdas
Expert 5K+
P: 8,127
Kindly post the code that you are working on.

what is the relation between the tables ?
Dec 8 '09 #2

nbiswas
100+
P: 149
Either u can go ahead with a job scheduler or write a window service that will monitor the application and will do the work within the t1 - t2 period
Dec 8 '09 #3

P: 25
@nbiswas: in fact, I am writting a web application.

Yep, I will describe it more specific.

At the beginning point I have a trigger as following:

Expand|Select|Wrap|Line Numbers
  1. CREATE TRIGGER AddRow
  2. ON Demo
  3. FOR INSERT
  4. AS 
  5.     UPDATE UpdateInfo    
  6.     set new=new+1
  7.     where id=1
This trigger is related to table Demo and table UpdateInfo. UpdateInfo is created as following:
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE UpdateInfo
  2. (ID    int    primary key,
  3. new    int,
  4. click    int)
  5.  
Customarily, whenever there is a row inserted to Demo, there is an update in field [new] of UpdateInfo, but I want that this trigger just works, for example, from 1:00 am to 6:00 am.

If this has been done, I then want to customize the time from an aspx page.

That all I want to do but still do not know how.

Thanks for your helping!
Dec 8 '09 #4

ck9663
Expert 2.5K+
P: 2,878
You have to create a trigger in UpdateInfo. You can either:

1. Create an INSTEAD OF trigger, check the system date when it fires then issue a return if it's outside of the range you need.
2. Create a AFTER trigger, use TRANSACTION, check the system date and issue a ROLLBACK then RETURN if it's outside of range. If it's within the range, proceed with the trigger and issue a COMMIT at the end before issuing a RETURN.

Happy Coding!!!

--- CK
Dec 8 '09 #5

P: 25
Because I am a beginner in sql server, I will be very appreciate if you could give me a more specific example.

The most important thing I wonder now is how can I check the system time such as wether it is between 0:00 am and 6:am or not?

I have realized that sql server 2005 has keyword "time", but it doesn't seem to be a data type.
Dec 10 '09 #6

ck9663
Expert 2.5K+
P: 2,878

P: 25
Your help is so useful!

Finally, I used AFTER TRIGGER, and to check time I used datepart() function.

Have a good day!
Dec 11 '09 #8

Post your reply

Sign in to post your reply or Sign up for a free account.