Trigger in a certain period of time of day

[Mai Phuong]

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

[debasisdas]

Kindly post the code that you are working on.

what is the relation between the tables ?

[nbiswas]

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

[Mai Phuong]

@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:

CREATE TRIGGER AddRow
ON Demo
FOR INSERT
AS 
    UPDATE UpdateInfo    
    set new=new+1
    where id=1

This trigger is related to table Demo and table UpdateInfo. UpdateInfo is created as following:

CREATE TABLE UpdateInfo
(ID    int    primary key,
new    int,
click    int)
 

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!

[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

[Mai Phuong]

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.

[ck9663]

Here's an article about the trigger.

Here are some basics of dates:

How SQL Programmers Choose their Dates

The Basics of Dates

How do SQL Server Programmers Search for Their Dates?

[Mai Phuong]

Your help is so useful!

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

Have a good day!