Results 1 to 25 of 25

Thread: Logging User Activity in MSSQL [RESOLVED]

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Sep 2004
    Location
    Jakarta, Indonesia
    Posts
    818

    Resolved 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
    Last edited by erickwidya; Jun 28th, 2005 at 09:07 PM.

    1st NF - a table should not contain repeating groups.
    2nd NF - any fields that do not depend fully on the primary key should be moved to another table.
    3rd NF - there should be no dependency between non key fields in same table.
    - E. Petroutsos -


    eRiCk

    A collection of "Laku-abis" Ebook, Permanent Residence

    Access Reserved Words, a Classic Form Bug, Access Limitation, Know run Process and the Lock they hold in, Logging User Activity in MSSQL,
    Kill Database Processes

  2. #2
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Logging User Activity in MSSQL

    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.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  3. #3
    Frenzied Member oceanebelle's Avatar
    Join Date
    Jun 2005
    Location
    my n00k.
    Posts
    1,064

    Re: Logging User Activity in MSSQL

    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.

  4. #4
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Logging User Activity in MSSQL

    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.
    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?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  5. #5
    Fanatic Member vb_dba's Avatar
    Join Date
    Jun 2001
    Location
    Somewhere aloft between the real world and insanity
    Posts
    1,016

    Re: Logging User Activity in MSSQL

    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.
    Chris

    Master Of My Domain
    Got A Question? Look Here First

  6. #6
    Fanatic Member kaffenils's Avatar
    Join Date
    Apr 2004
    Location
    Norway
    Posts
    946

    Re: Logging User Activity in MSSQL

    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.

  7. #7
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Logging User Activity in MSSQL

    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

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  8. #8
    Frenzied Member oceanebelle's Avatar
    Join Date
    Jun 2005
    Location
    my n00k.
    Posts
    1,064

    Re: Logging User Activity in MSSQL

    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

  9. #9
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Logging User Activity in MSSQL

    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.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  10. #10
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,522

    Re: Logging User Activity in MSSQL

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  11. #11
    Fanatic Member kaffenils's Avatar
    Join Date
    Apr 2004
    Location
    Norway
    Posts
    946

    Re: Logging User Activity in MSSQL

    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.

  12. #12

    Thread Starter
    Fanatic Member
    Join Date
    Sep 2004
    Location
    Jakarta, Indonesia
    Posts
    818

    Re: Logging User Activity in MSSQL

    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 , 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

    1st NF - a table should not contain repeating groups.
    2nd NF - any fields that do not depend fully on the primary key should be moved to another table.
    3rd NF - there should be no dependency between non key fields in same table.
    - E. Petroutsos -


    eRiCk

    A collection of "Laku-abis" Ebook, Permanent Residence

    Access Reserved Words, a Classic Form Bug, Access Limitation, Know run Process and the Lock they hold in, Logging User Activity in MSSQL,
    Kill Database Processes

  13. #13
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Logging User Activity in MSSQL

    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.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  14. #14
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,522

    Re: Logging User Activity in MSSQL

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  15. #15

    Thread Starter
    Fanatic Member
    Join Date
    Sep 2004
    Location
    Jakarta, Indonesia
    Posts
    818

    Re: Logging User Activity in MSSQL

    thanks Tg, szlamany

    How many tables do you need this level of tracking on?
    what about all Tables in my current database? (sound little greedy ) now i got 8, maybe more when i done implemented in app

    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

    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?
    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

    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

    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

    1st NF - a table should not contain repeating groups.
    2nd NF - any fields that do not depend fully on the primary key should be moved to another table.
    3rd NF - there should be no dependency between non key fields in same table.
    - E. Petroutsos -


    eRiCk

    A collection of "Laku-abis" Ebook, Permanent Residence

    Access Reserved Words, a Classic Form Bug, Access Limitation, Know run Process and the Lock they hold in, Logging User Activity in MSSQL,
    Kill Database Processes

  16. #16
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,522

    Re: Logging User Activity in MSSQL

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  17. #17

    Thread Starter
    Fanatic Member
    Join Date
    Sep 2004
    Location
    Jakarta, Indonesia
    Posts
    818

    Re: Logging User Activity in MSSQL

    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

    1st NF - a table should not contain repeating groups.
    2nd NF - any fields that do not depend fully on the primary key should be moved to another table.
    3rd NF - there should be no dependency between non key fields in same table.
    - E. Petroutsos -


    eRiCk

    A collection of "Laku-abis" Ebook, Permanent Residence

    Access Reserved Words, a Classic Form Bug, Access Limitation, Know run Process and the Lock they hold in, Logging User Activity in MSSQL,
    Kill Database Processes

  18. #18
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Logging User Activity in MSSQL

    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.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  19. #19

    Thread Starter
    Fanatic Member
    Join Date
    Sep 2004
    Location
    Jakarta, Indonesia
    Posts
    818

    Re: Logging User Activity in MSSQL

    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

    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

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

    1st NF - a table should not contain repeating groups.
    2nd NF - any fields that do not depend fully on the primary key should be moved to another table.
    3rd NF - there should be no dependency between non key fields in same table.
    - E. Petroutsos -


    eRiCk

    A collection of "Laku-abis" Ebook, Permanent Residence

    Access Reserved Words, a Classic Form Bug, Access Limitation, Know run Process and the Lock they hold in, Logging User Activity in MSSQL,
    Kill Database Processes

  20. #20
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Logging User Activity in MSSQL

    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!

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  21. #21
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Logging User Activity in MSSQL

    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.
    Last edited by szlamany; Jun 25th, 2005 at 12:32 PM.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  22. #22

    Thread Starter
    Fanatic Member
    Join Date
    Sep 2004
    Location
    Jakarta, Indonesia
    Posts
    818

    Re: Logging User Activity in MSSQL

    thanks szlamany

    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?

    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
    ...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

    1st NF - a table should not contain repeating groups.
    2nd NF - any fields that do not depend fully on the primary key should be moved to another table.
    3rd NF - there should be no dependency between non key fields in same table.
    - E. Petroutsos -


    eRiCk

    A collection of "Laku-abis" Ebook, Permanent Residence

    Access Reserved Words, a Classic Form Bug, Access Limitation, Know run Process and the Lock they hold in, Logging User Activity in MSSQL,
    Kill Database Processes

  23. #23

    Thread Starter
    Fanatic Member
    Join Date
    Sep 2004
    Location
    Jakarta, Indonesia
    Posts
    818

    save to .txt as result from SELECT query

    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
    Last edited by erickwidya; Jun 27th, 2005 at 05:00 AM.

    1st NF - a table should not contain repeating groups.
    2nd NF - any fields that do not depend fully on the primary key should be moved to another table.
    3rd NF - there should be no dependency between non key fields in same table.
    - E. Petroutsos -


    eRiCk

    A collection of "Laku-abis" Ebook, Permanent Residence

    Access Reserved Words, a Classic Form Bug, Access Limitation, Know run Process and the Lock they hold in, Logging User Activity in MSSQL,
    Kill Database Processes

  24. #24

    Thread Starter
    Fanatic Member
    Join Date
    Sep 2004
    Location
    Jakarta, Indonesia
    Posts
    818

    Re: save to .txt as result from SELECT query

    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

    1st NF - a table should not contain repeating groups.
    2nd NF - any fields that do not depend fully on the primary key should be moved to another table.
    3rd NF - there should be no dependency between non key fields in same table.
    - E. Petroutsos -


    eRiCk

    A collection of "Laku-abis" Ebook, Permanent Residence

    Access Reserved Words, a Classic Form Bug, Access Limitation, Know run Process and the Lock they hold in, Logging User Activity in MSSQL,
    Kill Database Processes

  25. #25

    Thread Starter
    Fanatic Member
    Join Date
    Sep 2004
    Location
    Jakarta, Indonesia
    Posts
    818

    Re: Logging User Activity in MSSQL

    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

    1st NF - a table should not contain repeating groups.
    2nd NF - any fields that do not depend fully on the primary key should be moved to another table.
    3rd NF - there should be no dependency between non key fields in same table.
    - E. Petroutsos -


    eRiCk

    A collection of "Laku-abis" Ebook, Permanent Residence

    Access Reserved Words, a Classic Form Bug, Access Limitation, Know run Process and the Lock they hold in, Logging User Activity in MSSQL,
    Kill Database Processes

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width