SQL Server 2008 - Binding another column's default value to the primary key

I'd like to know how I can best implement the following simple strategy for row versioning in SQL Server 2008. The idea is to copy the prima开发者_运维技巧ry_key field to another column (originationg_id in this case) in order to group together multiple versions/revisions of the same object.

When I insert the initial "version1" row, I want to default the originating_id column to that of the primary_key. This is generated automatically by the database, so I'm not sure how to go about it. Subsequent inserts will already know the value to supply for this field.

Example:

primary_key, originating_id, date_created, some_value
---------------------------------------------------------------------
1            1               13/12/2010    version1 of object A...
2            1               14/12/2010    version2 of object A...
3            1               15/12/2010    version3 of object A...
4            4               15/12/2010    version1 of object B...

Thanks.

To me it looks like this is a table that is stored along side the actual data, you can do a few things with this depending on how you are controlling your audit.

1. You can use SELECT SCOPE_IDENTITY() to grab the identity value after the insert to the table, and then use that value to do the insert into your reporting table
2. You can use a trigger on "after insert" and then you will have access to the primary key.

If you are doing this inside of the same table for some odd reason, I would recommend potentially looking at a different strategy, as it will be hard to query the "most current" version of this long term.

As Mitchel Sellers suggests, I would use an after insert trigger; in that trigger you would use the 'inserted' table for the originating id and possible the some_value column. The trigger would be something like:

CREATE TRIGGER triggername 
   ON  tablename
   AFTER insert
AS 
BEGIN

declare @somevalue varchar(max)
declare @originalid int
select @somevalue = '<some query to set the description>'
select @originalid = inserted.tablenameid

insert into audittable
select @originalid, getdate(), @somevalue


END