Row updated?

  • 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

  • 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

    http://www.sqlservercentral.com/columnists/awarren/

  • Have you considered using the BINARY_CHECKSUM(*) Function -

  • 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