Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2004
    Posts
    6

    Unanswered: Tracking all table modifcations

    I am looking for an efficient way to track all modications to a a database. I have come up with a few ideas but none are very efficient.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Data or objects?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Apr 2004
    Posts
    6
    Data Only, We want to be able to see any changes that have been made up to a few months back

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    We create an indentical table for every table we want to tracjk history on.

    We just add _H to the end of the name..

    Now on the base table we add a trigger. Since all the current data is stored in that table, we don't worry about INSERTS...we only track UPDATES and DELETES, and we don't worry which column was modified, we move the whole row.

    We also add 3 additional administrative columns to the history table..

    Here's a sample trigger

    Code:
    
    CREATE TRIGGER Company_UpdTr ON Company
    FOR UPDATE, DELETE 
    AS
    
    If Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted)
    	BEGIN
    
    		Insert Into Company_H (
    		       HIST_ADD_TYPE
    		      ,HIST_ADD_BY
    		      ,HIST_ADD_SYSUSER_BY
    		      ,Company_Name
    		      ,Active_Ind
    		      ,Psft_Company_Id
    		      ,FEIN
    		      ,Has_NonQual_Taxes
    		      ,Has_Qual_Taxes
    		      ,Created_By
    		      ,Created_Ts
    		      ,Updated_By
    		      ,Updated_Ts
    		)
    		Select
    		       'U'
    		      ,(Select Inserted.Updated_By from Inserted 
    			Where Deleted.Company_Name	= Inserted.Company_Name)
    		      ,user
    		      ,Company_Name
    		      ,Active_Ind
    		      ,Psft_Company_Id
    		      ,FEIN
    		      ,Has_NonQual_Taxes
    		      ,Has_Qual_Taxes
    		      ,Created_By
    		      ,Created_Ts
    		      ,Updated_By
    		      ,Updated_Ts	  
    		From  Deleted	   
    	END
    
    
    If Not Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted)
    
    	BEGIN
    
    		Insert Into Company_H (
    		       HIST_ADD_TYPE
    		      ,HIST_ADD_BY
    		      ,HIST_ADD_SYSUSER_BY
    		      ,Company_Name
    		      ,Active_Ind
    		      ,Psft_Company_Id
    		      ,FEIN
    		      ,Has_NonQual_Taxes
    		      ,Has_Qual_Taxes
    		      ,Created_By
    		      ,Created_Ts
    		      ,Updated_By
    		      ,Updated_Ts
    		)
    		Select
    		       'D'
    		      ,user
    		      ,user
    		      ,Company_Name
    		      ,Active_Ind
    		      ,Psft_Company_Id
    		      ,FEIN
    		      ,Has_NonQual_Taxes
    		      ,Has_Qual_Taxes
    		      ,Created_By
    		      ,Created_Ts
    		      ,Updated_By
    		      ,Updated_Ts	  
    		From  Deleted	
    	END
    Whatcha think?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Apr 2004
    Posts
    6
    Thanx gives me somewhere to start.

  6. #6
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Thats a valid idea from Brett, if the budget permits then you can deploy Lumigent's Entegra to audit the database or Log explorer to read the Tlog to track the changes.

    HTH
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  7. #7
    Join Date
    Apr 2004
    Posts
    10

    Angry Sql Authentication

    You must also pre-plan for auditing tables... you need to make sure the users login with individual sql usernames or you will need to use NT authentication..

    far too many systems get implemented these days with users using the same username and password (be it underlying ConnectionString or through query analyser)

Posting Permissions

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