Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Tracking Data changes

    Hi guys I need to create a kind of Auditing Table that tracks Data changes, like Delete, Update, Insert and I was wondering how would I do that for this table. Any help would be appreciated

    Code:
    CREATE TABLE [dbo].[EmployeeGamingLicense](
    	[REHIRE] [datetime] NULL CONSTRAINT [DF_EmployeeGamingLicense_REHIRE]  DEFAULT ((0)),
    	[CONDITIONAL] [nvarchar](50) NULL CONSTRAINT [DF_EmployeeGamingLicense_CONDITIONAL]  DEFAULT (N'No'),
    	[DATEOFCONDITIONAL] [datetime] NULL CONSTRAINT [DF_EmployeeGamingLicense_DATEOFCONDITIONAL]  DEFAULT ((0)),
    	[INVESTIGATOR_COND] [nvarchar](50) NULL CONSTRAINT [DF_EmployeeGamingLicense_INVESTIGATOR_COND]  DEFAULT (N'N/A'),
    	[STATUS] [nvarchar](50) NULL CONSTRAINT [DF_EmployeeGamingLicense_STATUS]  DEFAULT (N'ACTIVE'),
    	[CAT] [nvarchar](50) NULL CONSTRAINT [DF_EmployeeGamingLicense_CAT]  DEFAULT (N'TEMP'),
    	[TM#] [int] NOT NULL CONSTRAINT [DF_EmployeeGamingLicense_TM#]  DEFAULT ((0)),
    	[LASTNAME] [nvarchar](255) NULL,
    	[FIRSTNAME] [nvarchar](255) NULL,
    	[SSN#] [int] NULL CONSTRAINT [DF_EmployeeGamingLicense_SSN#]  DEFAULT ((0)),
    	[DEPT#] [nvarchar](50) NULL,
    	[HIREDATE] [datetime] NULL,
    	[JOBTITLE] [nvarchar](50) NULL,
    	[EMPLOYEENOTES] [nvarchar](max) NULL,
    	[NIGCLICAPPRVD] [datetime] NULL,
    	[FINALSUITDONE] [datetime] NULL,
    	[NOTES] [nvarchar](max) NULL,
    	[REASONFORCOND] [nvarchar](50) NULL,
    	[SSNLastFour]  AS (reverse(CONVERT([char](4),reverse([SSN#]),(0)))),
    	[FINALBYYR]  AS (datepart(year,[FINALSUITDONE])),
    	[FINALBYMNTH]  AS (datepart(month,[FINALSUITDONE])),
     CONSTRAINT [PK_EmployeeGamingLicense] PRIMARY KEY CLUSTERED 
    (

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Try running this on for your table:
    sqlblindman private pastebin - collaborative debugging tool

    Generates a script for creating an archive table and triggers on your production table to maintain it.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Talking

    Quote Originally Posted by blindman
    Try running this on for your table:
    sqlblindman private pastebin - collaborative debugging tool

    Generates a script for creating an archive table and triggers on your production table to maintain it.

    Blindman that was cool ...I LIKE IT. You have got some serious skills

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Actually, I'm motivated primarily by laziness.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Quote Originally Posted by blindman
    Actually, I'm motivated primarily by laziness.

    Still Blindman I wish I had your skills, that is awesome

  6. #6
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by desireemm
    Hi guys I need to create a kind of Auditing Table that tracks Data changes, like Delete, Update, Insert and I was wondering how would I do that for this table. Any help would be appreciated
    Change Data Capture

  7. #7
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Thank you so much guys for your help

Posting Permissions

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