Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
wkm1925
Posting Yak Master
207 Posts |
 Posted - 2008-05-14 : 01:23:17
|
| I've have following table,tblHIT - this table will reveive record every minutesDate | Time | Main_ID | Hit------------------------------------3/1/2006 | 100 | 200 | 8700.........4/14/2008 | 100 | 200 | 45004/14/2008 | 100 | 201 | 87004/14/2008 | 200 | 200 | 35004/14/2008 | 300 | 201 | 7700......I've also 1 job in SQL Server Agent will execute STORED PROCEDURE and will filter 7 days record before from current date and insert it into tblHIT_7days. tblHIT_7days shown as followtblHIT_7daysDate | Time | Main_ID | Hit------------------------------------3/7/2008 | 100 | 200 | 8700.........4/13/2008 | 100 | 200 | 45004/13/2008 | 100 | 201 | 87004/13/2008 | 200 | 200 | 35004/13/2008 | 300 | 201 | 7700I've plan to create TRIGGER. This TRIGGER will insert the appropriate record from tblHIT into tblHIT_7days and also delete a appropriate record in tblHIT_7days. This INSERT and DELETE transaction depend on current date.Is it possible, TRIGGER can do this? |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-05-15 : 15:37:41
|
| Yes, a trigger is just a stored procedure that fires based on a table event (ins/upd/del) so it can insert and delete rows based on getdate(). The question is are you sure you want to this logic as part of a trigger? If you are trying to maintain a weeks worth of data at a time it doesn't seem that you would want that controlled by a trigger. Why do you want to replace the job?Be One with the OptimizerTG |
 |
|
|
wkm1925
Posting Yak Master
207 Posts |
Posted - 2008-05-15 : 23:15:16
|
| Kindest Mr. TG,What technology in SQL Server 2005 can do the above job if trigger doesn't seem not appropriate to controll this? |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2008-05-16 : 03:08:14
|
| A weekly SQL scheduled job, launching a stored procedure using the SQL Agent tool. |
|
|
|
wkm1925
Posting Yak Master
207 Posts |
Posted - 2008-05-16 : 04:08:37
|
| If create job in SQL Server Agent. I've plan to run this job everyday in every 5 minutes. Is that ok in term source of I/O overhead? |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-05-16 : 07:54:35
|
| If the table is only written to ONCE every 5 minutes and by only one process then it makes no difference if it is a job or a trigger. Usually a table is written to constantly and by multiple users and in that case you would not want to use a trigger to maintain the other table but rather a scheduled job.Be One with the OptimizerTG |
|
|
|
wkm1925
Posting Yak Master
207 Posts |
Posted - 2008-05-16 : 22:17:48
|
| Kindest all,Tq very much for the info. :) |
|
|
|
|
|
|
Is it this job can program in trigger?