Post reply

Transactions logging

  • October 26, 2004 at 2:11 pm

    #84485

    Hi,

    I need to log some transactions that occur in the database. For example, a change of patient name. I know I can implement an UPDATE trigger on the Patient table and write the following code:

    INSERT INTO TransactionsLogTable (cTransaction)

        SELECT 'Change in patient name : OLD = ' +

                   Deleted.PatientName + ' NEW = ' +

                   Inserted.PatientName

        FROM Inserted

        INNER JOIN Deleted ON Inserted.PatientID = Deleted.PatientID AND

                                        Inserted.PatientName <> Deleted.PatientName

    but this can rapidly become a bottleneck if I want to trace like 20 fields on the same table. Using this method, I will have to perform 20 INSERT - SELECT

    statements, each with a different condition.

    Is there a better way to do this?

    I know that Oracle supports the creation of triggers like this:

     CREATE TRIGGER test_mfa 

     BEFORE UPDATE OF colonne_1, colonne_3 ON table_test_mfa

     DECLARE var_1 number;

     BEGIN

        ......

     END;

    Can we find some equivalent in SQL Server?

    Thx.

    Yanick

  • October 27, 2004 at 11:14 am

    #528197

    Triggers are the best way to do that, unless you can rewrite the application(s) to record changes.  If you *need* to record changes, why are you worried about a performance hit?  If it's just an option, then don't do it if performance suffers too much.

    So long, and thanks for all the fish,

    Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply