Logging User Activity in MSSQL [RESOLVED]

currently i'm using TRIGGER for INSERT, DELETE, UPDATE which Insert new record to tblLogs
the table structure = UserName | DateTime | ActionTaken (I, D, U) | TableName

everything works fine until i realize that i missed important thing which is the "ColumnName" that user changed coz in here User seem easily forgotten what they have done, that's why i planning using the tblLogs

so the question would be How (if not involved complicated method)? or any other better method?

i open for any idea

thanks

I've always told my customers that we would not do "change transaction" logging in our systems. The expense of triggers is too great.

We always tell them to find a 3rd party product to do the logging of who/what/where/when/how. Not sure any of them have purchased anything yet, but that's our position.

so you're planning on adding a columnname field to your tblLogs?...

uhmm just one question is this one column only or a delimiter separated list of column names changed by the user?

you could user IS UPDATED() function. :D

Quote:

---

Originally Posted by oceanebelle

so you're planning on adding a columnname field to your tblLogs?...

uhmm just one question is this one column only or a delimiter separated list of column names changed by the user?

you could user IS UPDATED() function. :D

---

What is the "IS UPDATED() function? I do not find that in BOL.

How would someone go about checking which columns are changed in an UPDATE trigger?

You can use the Update() function to check if the value of a column has changed:

Code:

---

CREATE TRIGGER myTRIGGER ON myTABLE FOR INSERT, UPDATE
AS

If Update(Col1)
Begin
    --Do some processing
End
...

---

Or, szlamany suggested, you could get a 3rd party app to handle all of this for you. Entegra from Lumigent is such a tool that allows you to audit database activitiy.

A function named COLUMNS_UPDATED() is available in triggers. It returns a varbinary bit pattern representing one column per bit with value 1 if updated and 0 if not updated.

Read more about this function in BOL in the CREATE TRIGGER topic.

vb_dba - wow - what a huge amount of work that would be - each column it's own IF block...

I knew I was avoiding trigger-logs for a reason :)

Sorry, added the d...
there's actually another function the COLUMNS_UPDATED.
I guess I mixed it there a bit..

But I did found IF UPDATE() on BOL ;)
Its in the index ... type in IF UPDATE

Quote:

---

Originally Posted by oceanebelle

Sorry, added the d...
there's actually another function the COLUMNS_UPDATED.
I guess I mixed it there a bit..

But I did found IF UPDATE() on BOL ;)
Its in the index ... type in IF UPDATE

---

Yes - I can see how with COLUMNS_UPDATED and a bit-wise loop, you could at least shorten the amount of code in the TRIGGER - but the expense of that operation seems too much.

If erickwidya is thinking of putting a row into a log file for each column that a user changed, then the UPDATE to the primary table itself will be burdened by all those additional INSERT's into the LOG TABLE.

Here's a real live trigger that we used here for this very purpose:

Code:

---

CREATE TRIGGER trg_tblTariffTransportVol_Update ON [dbo].[tblTariffTransportVol]
FOR UPDATE
AS

DECLARE @UserName varchar(255)
DECLARE @Description varchar(255)
DECLARE @KeyData int
DECLARE @UserID int
DECLARE @ControlID int

SET @UserName=SYSTEM_USER
SET @Description = ''
SET @KeyData = 0
SET @UserID = 0

/*Make sure we have a current user in the event that the calling application skips it */
SELECT @UserID=UserID FROM tblSecurityGrouping WHERE LogonName = @UserName
SELECT @ControlID=ControlID FROM tblControls WHERE ProgID = 'TariffEntry.ucTariffEntry'

        SET @Description = ''

        IF UPDATE(ChargeName)
          BEGIN
                SELECT @Description = @Description + CHAR(13) + CHAR(10) + 'Change Name'

                SELECT @Description = @Description + CHAR(13) + CHAR(10) + 'Old: ' + ISNULL(del.ChargeName, '')
                FROM deleted del

                SELECT @Description = @Description + CHAR(13) + CHAR(10) + 'New: ' + ISNULL(ins.ChargeName, '')
                FROM inserted ins
          END

        IF UPDATE(CommodityCharge)
          BEGIN
                SELECT @Description = @Description + CHAR(13) + CHAR(10) + 'Commodity Charge'

                SELECT @Description = @Description + CHAR(13) + CHAR(10) + 'Old: ' + CASE ISNULL(del.CommodityCharge, 0) WHEN 0 THEN 'No' ELSE 'Yes' END
                FROM deleted del

                SELECT @Description = @Description + CHAR(13) + CHAR(10) + 'New: ' + CASE ISNULL(ins.CommodityCharge, 0) WHEN 0 THEN 'No' ELSE 'Yes' END
                FROM inserted ins
          END

        IF UPDATE(UnitType)
          BEGIN
                SELECT @Description = @Description + CHAR(13) + CHAR(10) + 'Unit'

                SELECT @Description = @Description + CHAR(13) + CHAR(10) + 'Old: ' + ISNULL(U.[Description], '') + ' (' + ISNULL(UT.UnitTypeName, '') + ')'
                FROM deleted del
                INNER JOIN tblUnit U ON del.UnitType = U.UnitId
                INNER JOIN tblUnitType UT ON U.UnitType = UT.UnitType

                SELECT @Description = @Description + CHAR(13) + CHAR(10) + 'New: ' + ISNULL(U.[Description], '') + ' (' + ISNULL(UT.UnitTypeName, '') + ')'
                FROM inserted ins
                INNER JOIN tblUnit U ON ins.UnitType = U.UnitId
                INNER JOIN tblUnitType UT ON U.UnitType = UT.UnitType
          END

        IF UPDATE(DisplayCharge)
          BEGIN
                SELECT @Description = @Description + CHAR(13) + CHAR(10) + 'Display Charge'

                SELECT @Description = @Description + CHAR(13) + CHAR(10) + 'Old: ' + CASE ISNULL(del.DisplayCharge, 0) WHEN 0 THEN 'No' ELSE 'Yes' END
                FROM deleted del

                SELECT @Description = @Description + CHAR(13) + CHAR(10) + 'New: ' + CASE ISNULL(ins.DisplayCharge, 0) WHEN 0 THEN 'No' ELSE 'Yes' END
                FROM inserted ins
          END

        SELECT @KeyData = ins.TariffTransID
        FROM inserted ins

        IF UPDATE(ChargeName) OR UPDATE(CommodityCharge) OR UPDATE(UnitType) OR UPDATE(DisplayCharge)
          BEGIN
                SELECT @Description = 'Updated Volumetric: ' + @Description
                FROM inserted ins

                INSERT INTO tblLogEntry ([TimeStamp], UserID, LogType, [Description], ControlID, KeyData)
                VALUES(GETDATE(), @UserID, 2, @Description, @ControlID, @KeyData)
          END

GO

---

It uses the UPDATE() fn, and yes.... it's a pain to have to do it for each field in the table.... but on the other hand, you only have to do it on the fields you really want to check.... In the above example, there's several more cols that could be changed but we only wanted to track the changing of certain key fields.

Tg

Quote:

---

Originally Posted by szlamany

Yes - I can see how with COLUMNS_UPDATED and a bit-wise loop, you could at least shorten the amount of code in the TRIGGER - but the expense of that operation seems too much.

If erickwidya is thinking of putting a row into a log file for each column that a user changed, then the UPDATE to the primary table itself will be burdened by all those additional INSERT's into the LOG TABLE.

---

Don't need to put a bitwise loop in the trigger. Simply add the inserted row into the log table along with the bit pattern returned by the COLUMNS_UPDATED(), and let the client that will read the log use the bit pattern to find out which columns were affected.

first, thanks for all replies

Quote:

---

Originally Posted by oceanebelle

uhmm just one question is this one column only or a delimiter separated list of column names changed by the user?

---

if i got all in separate column, in would hard to do it so i planned using delimiter so it would go in one column for all tables

Quote:

---

Originally Posted by szlamany

I've always told my customers that we would not do "change transaction" logging in our systems. The expense of triggers is too great.

---

argghh, thx for the information..

Quote:

---

Originally Posted by vb_dba and oceanebelle

IF UPDATE(columnName)

---

yes i'm familliar with IF UPDATE("columnName") but as szlamany said, it would be huge amount of work

Quote:

---

Originally Posted by kaffenils

A function named COLUMNS_UPDATED() is available in triggers. It returns a varbinary bit pattern representing one column per bit with value 1 if updated and 0 if not updated.

---

looks promising when i read it at BOL. is it gonna cost in performance?

Quote:

---

Originally Posted by BOL

IF (COLUMNS_UPDATED())
Tests, in an INSERT or UPDATE trigger only, whether the mentioned column or columns were inserted or updated. COLUMNS_UPDATED returns a varbinary bit pattern that indicates which columns in the table were inserted or updated.
The COLUMNS_UPDATED function returns the bits in order from left to right, with the least significant bit being the leftmost. The leftmost bit represents the first column in the table; the next bit to the right represents the second column, and so on. COLUMNS_UPDATED returns multiple bytes if the table on which the trigger is created contains more than 8 columns, with the least significant byte being the leftmost. COLUMNS_UPDATED will return the TRUE value for all columns in INSERT actions because the columns have either explicit values or implicit (NULL) values inserted.

---

as i saw at BOL but i don't understand what it means :confused:, can someone lighten it a bit?

Quote:

---

Originally Posted by techgnome

Here's a real live trigger that we used here for this very purpose:...

---

yes, this what i planned to do it but urs filled with more code than i thought :)

thanks

Ok - it's time to step back and explain what you want to do - what is the end goal.

How many tables do you need this level of tracking on?

How many columns in these tables?

Have you considered other options - like making the rows be more "transaction" like - a new row replaces an old row.

How often will changes be made to the data?

Do you need to know who did the change before the prior change?

Give us a summary - in few words - of what your goal is and who will be looking at this transaction log.

Quote:

---

Originally Posted by erickwidya

yes, this what i planned to do it but urs filled with more code than i thought :)

thanks

---

That's primarily because we needed to capture that amount of detail as we were combating claims from a client ("We swear, we didn't touch it... it must have been you guys.")

It just goes to show how indepth you can really go with it.

Tg

thanks Tg, szlamany

Quote:

---

How many tables do you need this level of tracking on?

---

what about all Tables in my current database? (sound little greedy :D) now i got 8, maybe more when i done implemented in app

Quote:

---

How many columns in these tables?

---

Columns is depend on the table, some of them got 5 Columns, some 3, some 2 so Tg's method to save old and new record seem can't be implemented

Quote:

---

Have you considered other options - like making the rows be more "transaction" like - a new row replaces an old row.

---

Quote:

---

How often will changes be made to the data?

---

at some table, it will often to change like for "Order", "Sale", "Return" which i had in one Table..some like "Master" table is less often

Quote:

---

Do you need to know who did the change before the prior change?

---

before the prior, i think not..i just need to see who responsible for the current change but if it can be doned like that it will be an advantage :)

as i explain earlier, now we had this program for Company's Sales, Order, Return, etc..sometimes when the data in tables is wrong, we find it difficult to track whose the last person who responsible for it - "Who Did What To Which Data When" (it's come from Lumigent that vb_dba mentioned) - so we planned to develop new program and got "Logging Activity" for started so the problem above will not become an issue

what i want is to be able to track all changes made to all tables included old value, new value, tablename, column changed, time, etc like what Log supposed to be if possible without cost alot in performance

the Tg's method is something that i can use but it's not global for all Tables since the Log table need same Column count like the Base table

Quote:

---

That's primarily because we needed to capture that amount of detail as we were combating claims from a client ("We swear, we didn't touch it... it must have been you guys.")

---

that's exactly how i feel if something got wrong but now i can't see who responsible for it but here we don't have client since it's for my company intern

now i'm able to use Trigger to log UserName, TableName, ColumnChanged, DateTime, LogType(D-Delete, U-Update, I-Insert)

thanks

nononono.... I don't save old and new records..... only the new data is actualy saved to the table. The "old" data gets concatenated into a longer string which then goes to the logtable. And the log table is our app's generic event log table that records everything that goes on in the system.

Example, this might be the result after a change to the DisplayCharge field:
Display Charge:
Old: No
New: Yes

And that text goes in to the LogEntry table, which also captures the data/time of the change and the user who did the change.

Tg

upss, sorry my mistake..ur method seem like what i'm using it now

Code:

---

CREATE TRIGGER tr_tblUnit
  ON tblUnit
  FOR INSERT, UPDATE, DELETE
AS
  DECLARE @DeletedCount int,
          @InsertedCount int,
          @ColumnChanged nvarchar(50)
 
  IF (COLUMNS_UPDATED() & 1 = 1 )
    SET @ColumnChanged = 'UnitID'
  ELSE IF (COLUMNS_UPDATED() & 2 = 2 )
    SET @ColumnChanged = 'Unit'
  ELSE IF (COLUMNS_UPDATED() & 3 = 3 )
    SET @ColumnChanged = 'IsConverted'
  ELSE IF (COLUMNS_UPDATED() & 4 = 4 )
    SET @ColumnChanged = 'CUnit'
  ELSE IF (COLUMNS_UPDATED() & 5 = 5 )
    SET @ColumnChanged = 'UnitDesc'
  ELSE
    SET @ColumnChanged = 'isDeleted'

  SET @DeletedCount = (SELECT COUNT(*) FROM Deleted)
  SET @InsertedCount = (SELECT COUNT(*) FROM Inserted)
           
  IF @InsertedCount > 0  BEGIN
    INSERT INTO tblLOG(UserID, CurrentDate, TableName, LOGType, ColumnChanged)
      SELECT CURRENT_USER, CURRENT_TIMESTAMP, 'tblUnit',
        CASE WHEN @DeletedCount > 0 THEN 'U'
        ELSE 'I'
        END,
      @ColumnChanged
  END
  ELSE BEGIN
    INSERT INTO tblLOG(UserID, CurrentDate, TableName, LOGType, ColumnChanged)
    SELECT CURRENT_USER, CURRENT_TIMESTAMP, 'tblUnit', 'D', @ColumnChanged
  END
GO

---

Quick point that I think needs to be made...

We do financial applications here - payable and receivable and payroll systems.

We do not allow changes to entries after batches are committed, closed and locked - that's kind of a general rule in all financial systems. Quantities and units and prices do not change.

For places where the user expects to be able to change values, we allow for the insert of additional rows - that when aggregated with the new rows, yield the final value the user wanted. That "transaction" based system is also pretty much a standard in the financial world. Original rows never change - new "related" rows are added - the final result being what the user wants. But the auditors are thrilled to see that "audit" trail.

All our tables (and I mean every one of them) has a TDATE field as the final column. In all our SPROCS that do an INSERT or UPDATE, we fill the TDATE with GETDATE() - so we have a time stamp of when the change was made.

Quote:

---

We do financial applications here - payable and receivable and payroll systems.

---

i want to make program that do that too but i had no basic skill in Accounting, so maybe in the future (soon i hope) until i learn the basic of Accounting ;)

Quote:

---

For places where the user expects to be able to change values, we allow for the insert of additional rows - that when aggregated with the new rows, yield the final value the user wanted.

---

not quite understand what this means

Quote:

---

Original rows never change - new "related" rows are added - the final result being what the user wants.

---

this one too

Here's a simple example of an accounting LEDGER TRANSACTION table using "transaction" thinking...

Code:

---

Transaction #    Transaction Date    Account #  Debit  Credit
-------------    ----------------    ---------  -----  ------
  000001            01/01/2005        1011      0.00  100.00
  000002            01/20/2005        2022      10.00    0.00
  000003            01/25/2005        1011      50.00    0.00
  000004            02/10/2005        3033      0.00  125.00
  000005            03/01/2005        2022      0.00    10.00

---

You can see by the example above that entries are made into a transaction table - the ACCOUNT # field relates rows. Using the SUM() function can aggregate the DEBIT and CREDIT figures for an ACCOUNT. Using the WHERE clause to restrict the date range allows you to see a balance "as-of any date".

This is a very simple example - just to show the transactional nature of entries.

No one changes entries - they add new entries.

Putting a USERNAME and DATETIME field in each row would allow you to track who did the entry and the exact date/time of the entry.

No triggers - no additional logic.

Simply architected in a way to allow for what the end goal is.

And more - since the transactional nature of the table make the auditors extremely happy!

Quote:

---

Originally Posted by kaffenils

Don't need to put a bitwise loop in the trigger. Simply add the inserted row into the log table along with the bit pattern returned by the COLUMNS_UPDATED(), and let the client that will read the log use the bit pattern to find out which columns were affected.

---

Actually - I like this idea a real lot. I didn't see this post earlier...

It would require that the "original" insert into the "real" table also get logged into the ARCHIVE table.

The ARCHIVE table would need a column for datetime and username of the change - along with the "bit" column for storing COLUMNS_UPDATED().

I could imagine using these three columns - DATETIME, USERNAME, COLUMNSAFFECTED as the primary key - seems likely it would be unique. [edit] Actually if multiple rows are changed at once, this would not be a unique key - maybe need a IDENTITY column to be the key... Maybe no index is actually better for a log table like this (just thinking out loud)...

Then the other columns - after these three columns - would match exactly the columns of the "real" table.

If the goal is to have this level of robust archive - then this is faster then looping in a trigger - checking fields.

Then I guessing the trigger would be something like:

Code:

---

Insert into ArchiveTable GetDate(),System_User,Columns_Updated(),* From Inserted

---

That single additional insert into the ARCHIVE table would serve the full purpose of tracking each column changed by a user.

thanks szlamany

Quote:

---

Here's a simple example of an accounting LEDGER TRANSACTION table using "transaction" thinking...

---

yes, that can be done for "transaction" table but what if the changes made to "master" table?

Quote:

---

Then the other columns - after these three columns - would match exactly the columns of the "real" table.

---

but my tables for the "logging stuff" doesn't have same column count, how can i manage to achieve this?

Quote:

---

Originally Posted by kaffenils

It returns a varbinary bit pattern representing one column per bit with value 1 if updated and 0 if not updated.

---

can't find this description in BOF

Quote:

---

Originally Posted by kaffenils

Simply add the inserted row into the log table along with the bit pattern returned by the COLUMNS_UPDATED(), ...

---

it took me sometime to figure it out what it means and how to do it :), it the same using POWER function though i'm not understand the desc of it
ex :if table1 got 3 columns, if user insert, update col 2, update col 2 and 3 then the COLUMNS_UPDATED() will return 7, 4, 6 this what u mean by saying

Quote:

---

...and let the client that will read the log use the bit pattern to find out which columns were affected.

---

right? how?

PS : sorry again if i asked question that seem the answer already clear, it just to convince me so i'm not having wrong assumption

thanks

just realize something..instead i "force" to use Tables the log, why don't i use text base (.txt) to save it so that method that kaffenils and szlamany's suggest can be implemented

Code:

---

Insert into ArchiveTable GetDate(),System_User,Columns_Updated(),* From Inserted

---

i think the bold part can be use now without have to worry about table's columns count anymore but how?

EDIT : which one is "do-able"?

i search in BOL and could not find any helpful information

thanks

i guess i give up for the "oldValue" and "newValue"
i already try BCP which seem promising but in order to do that u have to had the table for LOG first, can't use temporary table like Inserted or #temp
:Confused: :Sad:

conclusion : it's hard to develop Logging by ur own, use 3rd Party s/w :)

PS : i stick with TRIGGER that i post earlier until i figured it out other method

thanks all

argh, it can be done :)
i have this TRIGGER that i used

Code:

---

IF EXISTS (SELECT name FROM sysobjects
          WHERE name = 'trTable1' AND type = 'TR')
  DROP TRIGGER trTable1
GO

CREATE TRIGGER trTable1 ON table1
  FOR INSERT, UPDATE, DELETE
AS
  SET NOCOUNT ON
  DECLARE @ColumnCount int,
          @InitColumn int,
          @FieldName nvarchar(50),
          @TableName nvarchar(50),
          @UpdateDate nvarchar(21),
          @UserName nvarchar(50),
          @cmd nvarchar(2000),
          @LogType nvarchar(1)

  SELECT @TableName = 'table1'
  SELECT @UserName = SYSTEM_USER
  SELECT @UpdateDate  = CONVERT(nvarchar(8), GETDATE(), 112) + ' ' + CONVERT(nvarchar(12), GETDATE(), 114)
 
  -- save Inserted and Deleted to temporary table
  SELECT * INTO #ins FROM Inserted
  SELECT * INTO #del FROM Deleted


  -- set the LogType
  IF EXISTS(SELECT * FROM #ins) 
    IF EXISTS(SELECT * FROM #del)
      SELECT @LogType = 'U'
    ELSE
      SELECT @LogType = 'I'
  ELSE
    SELECT @LogType = 'D'
 
  -- get the column count for @TableName
  SELECT @InitColumn = 0, @ColumnCount = MAX(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName
 
  -- loop for each column 
  WHILE (@InitColumn < @ColumnCount) BEGIN

    -- set the initial column to start
    SELECT @InitColumn = MIN(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND ORDINAL_POSITION > @InitColumn

    -- get the column name for the initial column
    SELECT @FieldName = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND ORDINAL_POSITION = @InitColumn
   
    -- insert into tblLogs for each column
    SELECT @cmd =        'INSERT INTO tblLogs(TableName, FieldName, OldValue, NewValue, LogType, UserName, UpdateDate) '
    SELECT @cmd = @cmd +  'SELECT ''' + @TableName + ''', '
    SELECT @cmd = @cmd +          '''' + @FieldName + ''', '   

    SELECT @cmd = @cmd +          'd.' + @FieldName + ', '
    SELECT @cmd = @cmd +          'i.' + @FieldName + ', '

    SELECT @cmd = @cmd +          '''' + @LogType + ''', '
    SELECT @cmd = @cmd +          '''' + @UserName + ''', '
    SELECT @cmd = @cmd +          '''' + @UpdateDate + ''''

    SELECT @cmd = @cmd +          ' FROM #ins AS i FULL JOIN #del AS d ON i.' + @FieldName + ' = d.' + @FieldName
    SELECT @cmd = @cmd +          ' WHERE (i.' + @FieldName + ' IS NOT NULL AND d.' + @FieldName + ' IS NULL)'
    SELECT @cmd = @cmd +          ' OR (i.' + @FieldName + ' IS NULL AND d.' + @FieldName + ' IS NOT NULL)'

    EXEC (@cmd) -- always use () bracket
  END

  -- drop the temporary table
  DROP TABLE #ins
  DROP TABLE #del
GO

---

i changed few line, the author is Nigel Rivett from http://www.nigelrivett.net/SQLTrigge...ilTrigger.html
this is the script for create tblLogs

Code:

---

CREATE TABLE [tblLogs] (
        [RowID] [int] IDENTITY (1, 1) NOT NULL ,
        [TableName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__Logs__TableName__21B6055D] DEFAULT (''),
        [FieldName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__Logs__ColumnName__22AA2996] DEFAULT (''),
        [OldValue] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF__Logs__OldValue__239E4DCF] DEFAULT (''),
        [NewValue] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF__Logs__NewValue__24927208] DEFAULT (''),
        [LogType] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Logs_Type] DEFAULT (''),
        [UserName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__Logs__UserName__25869641] DEFAULT (''),
        [UpdateDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

---

PS: not sure for the perfomance though..i've to tried it first
thank you all