May 24, 2003 at 6:51 am
I am relatively new to SQL and I'm facing a problem that I need some guidance. Basically, a few tables of my SQL2000 database must be duplicated on another site. However, the procedure to transfer the 'inserts, updates, deletes' is via an Excel file (and this cannot be changed). Basically I'm trying to address the 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.
TIA,
John
May 24, 2003 at 8:23 am
Depends on whether you want to send every change, or just the net. If you're doing every change, then a trigger to log the change would probably make sense (log to a separate table the difference between inserted and deleted logical table values). If you're only syncing once a day, some type of flag would work. You could put a trigger to set a last modified date, set a dirty flag, etc. Rowversion will work too, but you'd have to keep a separate table (or column I suppose) to track the last version submitted.
Andy
May 26, 2003 at 1:42 am
Have you considered using the BINARY_CHECKSUM(*) Function -
May 26, 2003 at 5:02 am
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]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply