SQL Server 2008 - binding a different default value for a column to a primary key

Question

I would 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 primary_key field to another column (originationg_id in this case) to combine multiple versions / revisions of one and the same object.

When I insert the source row "version1", I want the default origining_id column to be equal to the primary_key column. This is automatically created by the database, so I'm not sure how to do this. Subsequent insertions will already know the delivery value 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...

Thank.

Answer 1

, , , , .

• SELECT SCOPE_IDENTITY(), ,
• " ", .

- , , " " .

Answer 2

Mitchel Sellers, ; "" some_value. :

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