Nachverfolgen von änderungen in einer SQL server 2005 Datenbank

Einen trigger hätte nicht alle Informationen, die Sie brauchen für eine Reihe von Gründen, aber keine Benutzer-id ist die Drahtreifen.

Ich würde sagen du bist auf dem richtigen Weg mit einem gemeinsamen sp einfügen, wo immer eine änderung vorgenommen wird. Wenn Sie die Standardisierung auf sp ' s für die Schnittstellen dann bist du vor dem Spiel - es wird schwer sein, zu schleichen, eine änderung, die nicht nachverfolgt.

Betrachten dies als das äquivalent von ein audit trail in einer accounting-Anwendung - das ist das Journal - eine einzelne Tabelle mit jeder Transaktion aufgezeichnet. Sie wäre nicht umzusetzen, separate Zeitschriften für Einzahlungen, Auszahlungen, Anpassungen, etc. und dies ist das gleiche Prinzip.

Ich würde vorschlagen, Sie zu nutzen, 2. Spalte ist in jeder Tabelle. Namen rowhistory und IsDeleted und der Datentyp xml und bit.
Nie löschen Sie die Zeilen, immer use-flag IsDeleted
Gehen Sie jetzt mit update-Trigger. Ich geben Sie beispielsweise für die gleiche
Ich habe diese Tabelle genannten Seite

    CREATE TABLE te_Page([Id] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](200) NOT NULL, [Description] [varchar](200) NULL,[CreatedBy] [uniqueidentifier] NULL, [CreatedDate] [datetime] NOT NULL, [UpdatedBy] [uniqueidentifier] NULL, [UpdatedDate] [datetime] NULL, [IsDeleted] [bit] NULL, [RowHistory] [xml] NULL, CONSTRAINT [PK_tm_Page] PRIMARY KEY CLUSTERED ([Id] ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ) ON [PRIMARY]

Nun nach dem erstellen der Tabelle alle Sie tun müssen ist, kopieren Sie den code unten, und Ihre Aufgabe ist gemacht für die Seite Tabelle. Es beginnt die Aufzeichnung der Geschichte der Reihe in der gleichen Zeile, die aktualisiert wird, zusammen mit alten und neuen Werten.

                ALTER Trigger [dbo].[Trg_Te_Page]    
        On [dbo].[te_Page]                
        After Update                
        As                 
        --If @@rowcount = 0 Or Update(RowHistory)    
        --Return    

        Declare @xml NVARCHAR(MAX)     
        Declare @currentxml NVARCHAR(MAX)   
        Declare @node NVARCHAR(MAX)    
        Declare @ishistoryexists XML    

        Declare @FormLineAttributeValueId int  

        -- new Values  
        Declare @new_Name varchar(200)  
        Declare @new_Description varchar(200)  

        Declare @new_CreatedBy UNIQUEIDENTIFIER    
        Declare @new_CreatedDate DATETIME    
        Declare @new_UpdatedBy UNIQUEIDENTIFIER    
        Declare @new_UpdatedDate DATETIME    
        Declare @new_IsDeleted BIT  

        --old values  
        Declare @old_Name varchar(200)  
        Declare @old_Description varchar(200)  

        Declare @old_CreatedBy UNIQUEIDENTIFIER    
        Declare @old_CreatedDate DATETIME    
        Declare @old_UpdatedBy UNIQUEIDENTIFIER    
        Declare @old_UpdatedDate DATETIME    
        Declare @old_IsDeleted BIT  


        -- declare temp fmId  
        Declare @fmId int  
        -- declare cursor  
        DECLARE curFormId cursor   
        FOR select Id from INSERTED   
        -- open cursor       
        OPEN curFormId  
        -- fetch row  
        FETCH NEXT FROM curFormId INTO @fmId  

        WHILE @@FETCH_STATUS  = 0   
        BEGIN   

        Select   
        @FormLineAttributeValueId = Id,   
        @old_Name = Name,  
        @old_Description = [Description],  

        @old_CreatedBy = CreatedBy,    
        @old_CreatedDate =CreatedDate,  
        @old_UpdatedBy =UpdatedBy,    
        @old_UpdatedDate =UpdatedDate,  
        @old_IsDeleted  = IsDeleted,  
        @currentxml = cast(RowHistory as NVARCHAR(MAX))  
        From DELETED where Id=@fmId  



        Select      
        @new_Name = Name,  
        @new_Description = [Description],  

        @new_CreatedBy = CreatedBy,    
        @new_CreatedDate =CreatedDate,  
        @new_UpdatedBy =UpdatedBy,    
        @new_UpdatedDate =UpdatedDate,  
        @new_IsDeleted  = IsDeleted  
        From INSERTED where Id=@fmId  

        set @old_Name = Replace(@old_Name,'&','&')
        set @old_Name = Replace(@old_Name,'>','>')  
        set @old_Name = Replace(@old_Name,'<','&lt;')     
        set @old_Name = Replace(@old_Name,'"','&quot;')
        set @old_Name = Replace(@old_Name,'''','&apos;')          

        set @new_Name = Replace(@new_Name,'&','&amp;')      
        set @new_Name = Replace(@new_Name,'>','&gt;')  
        set @new_Name = Replace(@new_Name,'<','&lt;')     
        set @new_Name = Replace(@new_Name,'"','&quot;')
        set @new_Name = Replace(@new_Name,'''','&apos;') 

        set @old_Description = Replace(@old_Description,'&','&amp;')
        set @old_Description = Replace(@old_Description,'>','&gt;')  
        set @old_Description = Replace(@old_Description,'<','&lt;')     
        set @old_Description = Replace(@old_Description,'"','&quot;')
        set @old_Description = Replace(@old_Description,'''','&apos;')          

        set @new_Description = Replace(@new_Description,'&','&amp;')      
        set @new_Description = Replace(@new_Description,'>','&gt;')  
        set @new_Description = Replace(@new_Description,'<','&lt;')     
        set @new_Description = Replace(@new_Description,'"','&quot;')
        set @new_Description = Replace(@new_Description,'''','&apos;')   

        set @xml = ''     

        BEGIN      

        -- for Name  
        If ltrim(rtrim(IsNull(@new_Name,''))) != ltrim(rtrim(IsNull(@old_Name,'')))    
        set @xml = @xml + '<ColumnInfo ColumnName="Name" OldValue="'+ @old_Name + '" NewValue="' + @new_Name + '"/>'    

        -- for Description  
        If ltrim(rtrim(IsNull(@new_Description,''))) != ltrim(rtrim(IsNull(@old_Description,'')))    
        set @xml = @xml + '<ColumnInfo ColumnName="Description" OldValue="'+ @old_Description + '" NewValue="' + @new_Description + '"/>'    

        -- CreatedDate     
        If IsNull(@new_CreatedDate,'') != IsNull(@old_CreatedDate,'')  
        set @xml = @xml + '<ColumnInfo ColumnName="CreatedDate" OldValue="'+ cast(isnull(@old_CreatedDate,'') as varchar(100)) + '" NewValue="' + cast(isnull(@new_CreatedDate,'') as varchar(100)) + '"/>'    

        -- CreatedBy     
        If cast(IsNull(@new_CreatedBy,'00000000-0000-0000-0000-000000000000')as varchar (36)) != cast(IsNull(@old_CreatedBy,'00000000-0000-0000-0000-000000000000')as varchar(36))    
        set @xml = @xml + '<ColumnInfo ColumnName="CreatedBy" OldValue="'+ cast(IsNull(@old_CreatedBy,'00000000-0000-0000-0000-000000000000') as varchar(36)) + '" NewValue="' + cast(isnull(@new_CreatedBy,'00000000-0000-0000-0000-000000000000') as varchar(36))+
        '"/>'    

        -- UpdatedDate       
        If IsNull(@new_UpdatedDate,'') != IsNull(@old_UpdatedDate,'')    
        set @xml = @xml + '<ColumnInfo ColumnName="UpdatedDate" OldValue="'+ cast(IsNull(@old_UpdatedDate,'') as varchar(100)) + '" NewValue="' + cast(IsNull(@new_UpdatedDate,'') as varchar(100)) + '"/>'    

        -- UpdatedBy     
        If cast(IsNull(@new_UpdatedBy,'00000000-0000-0000-0000-000000000000') as varchar(36)) != cast(IsNull(@old_UpdatedBy,'00000000-0000-0000-0000-000000000000') as varchar(36))    
        set @xml = @xml + '<ColumnInfo ColumnName="UpdatedBy" OldValue="'+ cast(IsNull(@old_UpdatedBy,'00000000-0000-0000-0000-000000000000') as varchar(36)) + '" NewValue="' + cast(IsNull(@new_UpdatedBy,'00000000-0000-0000-0000-000000000000') as varchar(36))+
        '"/>'    

        -- IsDeleted  
        If cast(IsNull(@new_IsDeleted,'') as varchar(10)) != cast(IsNull(@old_IsDeleted,'') as varchar(10))    
        set @xml = @xml + '<ColumnInfo ColumnName="IsDeleted" OldValue="'+ cast(IsNull(@old_IsDeleted,'') as varchar(10)) + '" NewValue="' + cast(IsNull(@new_IsDeleted,'') as varchar(10)) + '" />'    

        END    

        Set @xml = '<RowInfo TableName="te_Page" UpdatedBy="' + cast(IsNull(@new_UpdatedBy,'00000000-0000-0000-0000-000000000000') as varchar(50)) +  '" UpdatedDate="' + Convert(Varchar(20),GetDate()) + '">' + @xml + '</RowInfo>'    
        Select @ishistoryexists = RowHistory From DELETED     

        --print @ishistoryexists  


        If @ishistoryexists is null    
        Begin     
        Set @xml = '<History>' + @xml + '</History>'      
        Update te_Page    
        Set    
        RowHistory = @xml    
        Where     
        Id = @FormLineAttributeValueId    

        End    

        Else    
        Begin     
        set @xml = REPLACE(@currentxml, '<History>', '<History>' + @xml)  
        Update te_Page  
        Set  
        RowHistory = @xml  
        Where   
        Id = @FormLineAttributeValueId     
        End  


        FETCH NEXT FROM curFormId INTO @fmId  
        END   


        CLOSE curFormId  
        DEALLOCATE curFormId  

Nun, wenn Sie ausführen, aktualisieren Sie Ihre Daten gespeichert werden, rowhistory Spalte

Bauten wir unsere eigene und brauchte nur die Benutzer-und pc an jedem add/update gespeicherte Prozedur. dann ist es nur eine Frage der ursprünglichen Datensatz und dem füllen der Variablen und der Vergleich mit den übergebenen Variablen und Protokollierung der Daten an unseren Tisch.
für gelöscht, wir haben nur eine Kopie des ursprünglichen Tabellen + ein timestamp-Feld, so wird der Datensatz nie wirklich gelöscht und können jederzeit wiederhergestellt werden, die wir brauchen (offensichtlich ist der löschen-routine prüft für FK-Beziehungen und so).

hinzufügen/update-log-Tabelle aussieht
datetime,
table_name,
column_name,
record_id,
old_value,
neuer_wert,
user_id,
computer

wir nie einfügen von Nullen, so dass wir Sie zu konvertieren leeren Saiten, neue Einträge sind markiert mit '{neuer Eintrag}' in der old_value Spalte. record_id aus, wie viele Schlüssel-Spalten zur eindeutigen Identifizierung einzelner Datensatz ( Feld1 + '.' + Feld2 + ... )

Hatte, habe ich ein setup in SQL Server wo wir verwenden würden, Ansichten für den Zugriff auf unsere Daten, die behandeln würde, Einfügungen, Aktualisierungen und Löschungen mit INSTEAD OF-Triggern.

Beispiel: ein STATT LÖSCHEN - trigger für die Sicht würde markieren Sie die Datensätze in der zugrunde liegenden Tabelle als gelöscht, und die Ansicht gefiltert wurde, um nicht gelöschte Datensätze.

In allen auslöst, die wir aktualisiert das änderungsdatum und Benutzername. Das Problem ist, dass logs der Datenbank-Benutzer-Namen, das ist nicht das gleiche wie die ultimative Anwendung, die Benutzer-Namen.

Ansicht muss an ein schema gebunden sein, für diese zu arbeiten.