I'm not sure in which direction the guidance should go. If you want to know how to determine when a row has been updated you can you a trigger, like Andy suggested. What follows is an example of one of my Update triggers.

CREATE TRIGGER updKapitalanlagenummern

ON dbo.tblKapitalanlagenummern

FOR update AS

BEGIN

INSERT INTO auditKapitalanlagenummern

(audit_log_type,

audit_KapitalanlagenID,

audit_KaNr,

audit_WKN,

audit_Kapitalanlagenbezeichnung,

audit_RisikoklasseID,

audit_Emission,

audit_Fälligkeit,

audit_CiL,

audit_CiV,

audit_PbL,

audit_PbV,

audit_StrukturiertesProdukt,

audit_Derivate,

audit_RIC,

audit_RatingID,

audit_EmittentID,

audit_ErstelltAm,

audit_ErstelltVonID,

audit_GeändertAm,

audit_GeändertVonID,

audit_DeletedON,

audit_DeletedBy,

audit_Deleted,

audit_FRVFonds,

audit_isin_nr,

audit_in_index,

audit_is_sust)

SELECT 'OLD',

del.KapitalanlagenID,

del.[Ka-Nr],

del.WKN,

del.Kapitalanlagenbezeichnung,

del.RisikoklasseID,

del.Emission,

del.Fälligkeit,

del.CiL,

del.CiV,

del.PbL,

del.PbV,

del.StrukturiertesProdukt,

del.Derivate,

del.RIC,

del.RatingID,

del.EmittentID,

del.ErstelltAm,

del.ErstelltVonID,

del.GeändertAm,

del.GeändertVonID,

del.DeletedON,

del.DeletedBy,

del.Deleted,

del.FRVFonds,

del.isin_nr,

del.in_index,

del.is_sust

FROM deleted del

INSERT INTO auditKapitalanlagenummern

(audit_log_type,

audit_KapitalanlagenID,

audit_KaNr,

audit_WKN,

audit_Kapitalanlagenbezeichnung,

audit_RisikoklasseID,

audit_Emission,

audit_Fälligkeit,

audit_CiL,

audit_CiV,

audit_PbL,

audit_PbV,

audit_StrukturiertesProdukt,

audit_Derivate,

audit_RIC,

audit_RatingID,

audit_EmittentID,

audit_ErstelltAm,

audit_ErstelltVonID,

audit_GeändertAm,

audit_GeändertVonID,

audit_DeletedON,

audit_DeletedBy,

audit_Deleted,

audit_FRVFonds,

audit_isin_nr,

audit_in_index,

audit_is_sust)

SELECT 'NEW',

ins.KapitalanlagenID,

ins.[Ka-Nr],

ins.WKN,

ins.Kapitalanlagenbezeichnung,

ins.RisikoklasseID,

ins.Emission,

ins.Fälligkeit,

ins.CiL,

ins.CiV,

ins.PbL,

ins.PbV,

ins.StrukturiertesProdukt,

ins.Derivate,

ins.RIC,

ins.RatingID,

ins.EmittentID,

ins.ErstelltAm,

ins.ErstelltVonID,

ins.GeändertAm,

ins.GeändertVonID,

ins.DeletedON,

ins.DeletedBy,

ins.Deleted,

ins.FRVFonds,

ins.isin_nr,

ins.in_index,

ins.is_sust

FROM inserted ins

END

auditKapitalanlagenummern is basically a mirror table to the original with some additional fields who and when row changed. Each time an Update happens 2 new records are inserted in the audit table. One with original state and the second with changed values.

Note: tblKapitalanlagenummern is not a huge table. It contains some 200 records and is not updated frequently. For a huge table where data changes frequently this might be not suitable, because of performance, disk space...

Is this guidance going in the right direction?

Cheers,