Hi John,
quote:
problem of 'updates'. I want to somehow know when a row has been updated. I've been reading about triggers, timestamp (rowversion), programmactically set a flag, etc and at this point I would appreciate some guidance.
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,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]