Move records to another table after the expiry date

Hi,
I am designing a Maintenace contract database. tblAgreement,
tblMaintSystems, tblSchedule,tblVisits etc. Each is joined by the FK. Since
service contracts expires after a certain time, i would like to move the
expired contracts based on either time or by a string condition like
'Expired = Yes' to another table so that only Current contracts are visible
under the customers. In another tab i would like to list all the expired
contracts for the customer since we might need to know what type of activity
was done on the customer in the past too.
I understand we can use a trigger to do it, but doesnt know how to do it.
thanks for any responses.
kesk
NB : if anybody need the table structure, i can give it.My suggestion would be NOT to use a separate table for storing these
archive, if you have control over the source code add a column on the table
naming "Expired" or something like that or use the existing columns to query
the data right on the original table. If you perhpas use a view for your
query you can either decided what the criteria is that you want to "mark "
your contracts as expired.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"kesk" <kesk32@.yahoo.co.in> schrieb im Newsbeitrag
news:u8MIJgBbFHA.3400@.tk2msftngp13.phx.gbl...
> Hi,
> I am designing a Maintenace contract database. tblAgreement,
> tblMaintSystems, tblSchedule,tblVisits etc. Each is joined by the FK.
> Since
> service contracts expires after a certain time, i would like to move the
> expired contracts based on either time or by a string condition like
> 'Expired = Yes' to another table so that only Current contracts are
> visible
> under the customers. In another tab i would like to list all the expired
> contracts for the customer since we might need to know what type of
> activity
> was done on the customer in the past too.
> I understand we can use a trigger to do it, but doesnt know how to do it.
> thanks for any responses.
> kesk
> NB : if anybody need the table structure, i can give it.
>