May 17, 2008 at 9:54 pm
I'm having table as below,
StudentEnroll_Thismonth
TransID(AutoNumber) | StudentID | Subject | TransDate
------------------------------------------------
1 | 0021 | A890 | 4/1/2008
2 | 0021 | A830 | 4/1/2008
3 | 0025 | A890 | 4/1/2008
4 | 0025 | A112 | 4/1/2008
5 | 0026 | A545 | 4/1/2008
.............
.............
99 | 0021 | A900 | 4/30/2008
100 | 0021 | A902 | 4/30/2008
101 | 0025 | A900 | 4/30/2008
*The table above contains what subject taken by student
Let's say, GETDATE()=5/1/2008
If execute Trans-SQL below,
INSERT INTO StudentEnroll_ThisMonth(StudentID,Subject,TransDate) values(0023,'B328',GETDATE())
How the trigger looks like to make sure the data in table shown as follow,
StudentEnroll_Thismonth
TransID(AutoNumber) | StudentID | Subject | TransDate
-----------------------------------------------------------
120 | 0023 | B328 | 5/1/2008
*All the previous month record move to StudentEnroll_PreviousMonth automatically
StudentEnroll_PreviousMonth
TransID(AutoNumber) | StudentID | Subject | TransDate
------------------------------------------------
..................
..................
..................
200 | 0021 | A890 | 4/1/2008
201 | 0021 | A830 | 4/1/2008
203 | 0025 | A890 | 4/1/2008
204 | 0025 | A112 | 4/1/2008
205 | 0026 | A545 | 4/1/2008
.................
.................
301 | 0021 | A900 | 4/30/2008
302 | 0021 | A902 | 4/30/2008
303 | 0025 | A900 | 4/30/2008
Anyone can help me to show the trigger?
May 18, 2008 at 12:42 pm
Hi,
Can i ask why you need to seperate the data into different tables when you have a date column?
Would it not be easier to have a table that holds all historic data for subjects that students have signed up for and just change your select queries to use something like the following:
select * from mytable where month(date)=month(getdate())
That would show all data for the current month??
Matt.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply