Post reply

Using triggers for maintaining insert/update/delete history

  • September 28, 2005 at 2:23 am

    #91334

    Hi,

    I am trying to create a trigger which will maintain a history of all transactions

    (Insert/Update/Delete) on a specific table.

    For example :

    Trigger is to be created on table TABLE1.Columns in TABLE1 are :

    RecID (autogenerated integer)

    FirstName

    LastName

    Email

    AccessType

    UserID

    Whenever a user does any transaction on TABLE1, the trigger must insert a record in another

    table TABLE1HISTORY. The columns in TABLE1HISTORY are :

    HistoryID (autogenerated integer)

    FieldName (name of the field that was updated)

    FieldValue (value that was updated)

    Type (values can be I or U or D)

    UserID (from TABLE1)

    DateTimeStamp

    Has any one attenmpted this ? Any help would be appreciated.

    Cheers,

    Arun

  • September 28, 2005 at 3:35 am

    #593582

    It is possible, but it will take a lot of resources, because 1 table can have 3 trigger. You must think if the user access the same table in the same time and with the different transaction? You will have a deadlock transaction, its very risk.

    Btw, you can see BOL for creating trigger.

  • September 28, 2005 at 3:44 am

    #593587

    Yes. I have worked on this. and it will work fine.

  • September 28, 2005 at 6:37 am

    #593624

    A trigger can potentially LOCK a table until it is complete.  Depending on your environment you may wind up with BAD performance.  An alternative is to create stored-procedures that MUST be used instead of DIRECT access.

    Inside the SPs you have the INSERT INTO History and THEN I/U/D into the "real" table(s)



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

Viewing 4 posts - 1 through 3 (of 3 total)

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