Transaction Log

• kelvs

  Newbie

  Points: 1

  More actions

  November 4, 2003 at 4:49 am

  #101742

  Hi everyone, well i have been task in creating transaction log tables for the databases that we are using. I created the transaction log table which includes the user, date, and the xml field in ntext for the old and new data.

  CREATE TABLE TRANSLOG

  (

  ID int identity,

  UserID int,

  LogDate datetime,

  XMLOLD ntext,

  XMLNEW ntext

  )

  I plan to create triggers for all the tables that need to be logged. I tried using for xml to derive the xml code but i cant seem to find a way to save that data onto another table, thus i tried to create the xml code dynamically.

  I created a stored procedure which will go through all the tables and create the triggers that will generate the xml string by deriving the fields from the syscolumns table.

  Dim @SQLStringHeader varchar(8000)

  Set @SQLStringHeader = 'CREATE TRIGGER ' + @TriggerName + ' on [' + @TableName + ']' + Char(13)

  Set @SQLStringHeader = @SQLStringHeader + 'for insert, update, delete ' + Char(13)

  Set @SQLStringHeader = @SQLStringHeader + 'as' + Char(13)

  Set @SQLStringHeader = @SQLStringHeader + Char(13)

  The problem i am having is that, I have a table that has about 120 fields and the string for that xml is > 8000 characaters. Is there any way for me store all of these data in another variable? or maybe have another way of creating the 'create trigger' script that will ensure i am able to accomodate > 8000 characters for my exec string

• Steve Jones - SSC Editor

  SSC Guru

  Points: 740318

  More actions

  November 4, 2003 at 1:15 pm

  #480811

  Is it worth it? Why not jsut handle those as a one off?

  Steve Jones

  sjones@sqlservercentral.com

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

  The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/

  http://www.dkranch.net