| Author |
Topic |
|
TJ
Posting Yak Master
201 Posts |
 Posted - 2002-05-02 : 13:07:27
|
I'm using SQL Server 7.0I have two tables, Efile and Efile_History. Each have the same columns, etc.I need to move all the records from the Efile table to the Efile_History table on October 31st of each year that have the column group = the current year.I thought a trigger might work although I've not worked with them until today. I must admit that I'm not altogether sure how they work. Here's what I have so far:create trigger MoveEfilesToHistory on efile_HISTORY FOR INSERTASif datepart(month, getdate()) = '05' and datepart(month, getdate()) = '02' and efile.bbsgroup = '2000prep'BEGIN insert * from efile where bbsgroup = '2000prep'End It does not like the *. Do I need a row count? I read that it only fires one time. Will it move all of the records or just one?Thanks!TeresaEdited by - tj on 05/02/2002 13:11:33 |
|
|
LarsG
Constraint Violating Yak Guru
284 Posts |
Posted - 2002-05-02 : 13:17:45
|
| Using a trigger for this task is not a good idea. Schedule a job that will do the insert insteadThe syntax for insert looks likeinsert into efile_history select * from efile where bbsgroup = '2000prep' |
 |
|
|
TJ
Posting Yak Master
201 Posts |
Posted - 2002-05-02 : 13:26:16
|
quote:
Using a trigger for this task is not a good idea.
I can write an sproc that will do exactly what I want. However, is there a way to make it run automatically, each year on October 31st?Thanks again!Teresa |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-05-02 : 17:39:33
|
| You can always schedule a job that would run daily, check current date, and if the date isn't October 31st would do nothing... On October 31st, it would start your proc. I don't know if I'd recommend doing it this way though... My experience is that people tend to forget about the batch processes that run once a year. So you may find that someone has already run your proc manually by the time your automated schedule kicks off...---------------Strong SQL Developer wanted in the Boston area. Please e-mail if interested. |
|
|
|
TJ
Posting Yak Master
201 Posts |
Posted - 2002-05-02 : 17:51:15
|
quote:
You can always schedule a job that would run daily, check current date, and if the date isn't October 31st would do nothing... On October 31st, it would start your proc. I don't know if I'd recommend doing it this way though... My experience is that people tend to forget about the batch processes that run once a year. So you may find that someone has already run your proc manually by the time your automated schedule kicks off...
I did consider this, but wasn't sure of the reprecussions. Thanks for responding and letting me know. I think our best bet is to have a job set up, without a schedule, that includes the processes that need to be ran on October 31st and then run it manually.I was looking at the trigger option today. Would it work if I set up the job, and then set the trigger to execute it on 10/31?Thanks again! You're a PEACH!  Teresa |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-05-02 : 19:28:36
|
| I think you are confused as to what a database trigger is... Trigger is not something you can set to fire on a certain day. Basically trigger is a procedure, tied to a specific data manipulation operation on a specific table. In other words, you can create a trigger for DELETE on table authors... And this trigger will fire every time you run a delete statement on this table. You can also create triggers for INSERT and UPDATE... In SQL2k you can create "instead-of" triggers... Actually it is explained pretty well here: http://sqlteam.com/item.asp?ItemID=3850http://sqlteam.com/item.asp?ItemID=6494Anyhow, the point is -- triggers don't run at on a specific day/time. If you want something to run on a certain day -- you need to schedule a job.---------------Strong SQL Developer wanted in the Boston area. Please e-mail if interested. |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-05-02 : 20:00:09
|
| Another approach, based on Ilya's recommendation on the daily schedule would be to schedule this to run "MONTHLY on the LAST day of EVERY 1 Month". This would cut down on processing effort.As for whether people have manually run the step before your automation, hopefully you will write the steps such that a) running it again after it's already done will do no harm because the proc will find no source records and b) running the proc twice simultaneously will do no harm either. |
|
|
|
TJ
Posting Yak Master
201 Posts |
Posted - 2002-05-03 : 09:09:38
|
quote:
I think you are confused as to what a database trigger is...
I think you're absolutely right! :o)I thought a trigger could be fired by the date. I'll read those articles!Thanks!Teresa |
|
|
|
TJ
Posting Yak Master
201 Posts |
Posted - 2002-05-03 : 09:15:48
|
quote:
"MONTHLY on the LAST day of EVERY 1 Month". This would cut down on processing effort.a) running it again after it's already done will do no harm because the proc will find no source records and b) running the proc twice simultaneously will do no harm either.
I'll keep in mind the 'monthly schedule'. I did test to make sure that it doesn't matter how many times it's ran, or when, it will only change the column in the table on '10/31/year 11:59:00'. Did I tell everyone that our backend is written in DOS and Clipper? We're converting everything to SQL, ASP, etc this summer! Yea!!Thanks again for the prompt responses and the continued support! The group of people that support us in these forums are truly amazing and quite wonderful!  Teresa |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-03 : 09:18:54
|
| mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm Clipper! I LOVE Clipper! |
|
|
|
|
How to move data from one table to another