Implementing version scenario in sqlserver

hello every one,

i have a problem , i need to implement the version scenario for a record that is about to be updated.ie., whenever i update a particular record the updated information is to be stored in master table and the record prior to update is to be stored in the version table, so that i can fetch all the record information from the insertion of the record to the latest available update and also no.of updates

i have desinged in this way,

master_table:
Rec_Id,W,X,Y,Z,Date_modified,num_of_versions

version_table
ver_Id,Rec_Id,W,X,Y,Z, Date_modified

i am unable to get the query,,

can any one help me in this scenario.

thanks and regards,
mudragada.

I have implemented in this way.....
if any modifications its pleasure to hear.........

during insertion of the new record call this proc
ALTER PROCEDURE dbo.sp_InsertRecord

(
@FstName nvarchar(MAX),
@LstName nvarchar(MAX),
@sName nvarchar(MAX),
@dt_mod Datetime
)

AS
begin
Declare @masterId int;
//inserting the new record into the master table
Insert into master (Fname,Lname,Surname,Date_Modified) values (@FstName,@LstName,@sName ,@dt_mod )
//get the identity of the newly created record
select @masterId=@@IDENTITY
/*inserting the same record into version table for the copy of version */
Insert into version(master_Id,Fname,Lname,Surname,Date_Modifie d) values (@masterId,@FstName,@LstName,@sName ,@dt_mod )
/*updating master table num_of_ver column wth the count of new record*/
Update master set num_of_versions=(select count(*) from version where master_Id=@masterId) where master_Id=@masterId

END


during update of the record call this proc


ALTER PROCEDURE dbo.sp_updateInsRecord

(
@masterId int,
@FstName nvarchar(MAX),
@LstName nvarchar(MAX),
@sName nvarchar(MAX),
@dt_mod Datetime
)

AS

begin
/* updating the master table with the current update*/
Update master set Fname=@FstName,Lname=@LstName,Surname=@sName,Date_ Modified=@dt_mod where master_Id=@masterId

/*inserting the udated record into version table */
Insert into version(master_Id,Fname,Lname,Surname,Date_Modifie d) values (@masterId,@FstName,@LstName,@sName ,@dt_mod )

/*updating the version colum in master table*/
Update master set num_of_versions=(select count(*) from version where master_Id=@masterId) where master_Id=@masterId

END

hope this is the right way i have implemented if any suggestion post them..