Qual è il modo migliore per memorizzare l'autore delle modifiche e visualizzare le modifiche di ogni colonna nella tabella temporale in SQL Server?

Vorrei memorizzare la cronologia delle modifiche nella tabella e ricordare anche l'autore del cambiamento. Ho creato una tabella temporale. Questa tabella contiene la colonna ChangeAuthorId il cui valore I viene aggiornato ogni volta che la tabella viene aggiornata. Vorrei visualizzare ogni valore modificato separatamente in una riga di registro con data, valore precedente, nuovo valore, autore di modifiche e nome della colonna.

Di seguito è riportato un esempio di database e codice che funziona, ma mi piacerebbe farlo più semplice. Penso che non sia il modo perfetto per farlo, perché ad esempio potrei dimenticare di aggiungere l'autore del cambiamento. Hai qualche idea per farlo meglio? Forse ci sono alcune funzioni di convalida o di database speciali per questo?

So che chiedere un database per ogni autore di modifiche può essere una cattiva idea, ma è solo un esempio veloce.

Questa è la mia definizione della tabella di esempio:

CREATE TABLE Person   
( 
    PersonId int NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1,1), 
    Name varchar(50) NOT NULL,
    Email varchar(50) NOT NULL,
    Country varchar(50) NOT NULL,
    ChangeAuthorId int NULL,
    SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
    SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)   
)   
WITH    
(   
    SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.PersonHistory)   
);

Record tabella persone:

ErecorddellatabellaPersonHistory:

QuestoèuncodicediesempiopervisualizzarelemodificheapportatesuBartSimpsondachiunque:

namespaceTemporalTablesTest{classProgram{staticvoidMain(string[]args){using(MyDbContextcontext=newMyDbContext()){varbart=context.People.Single(p=>p.Name=="Bart Simpson");
                var bartHistory = context.PersonHistory.Where(x => x.PersonId == bart.PersonId).OrderByDescending(x => x.SysStartTime).ToList();

                // First element of history must be actual person values, so it can be easily compared in a loop below
                bartHistory.Insert(0, new PersonHistory
                {
                    Name = bart.Name,
                    Email = bart.Email,
                    Country = bart.Country,
                    ChangeAuthorId = bart.ChangeAuthorId,
                    SysStartTime = bart.SysStartTime,
                    SysEndTime = bart.SysEndTime
                });

                for (int i = 1; i < bartHistory.Count(); i++)
                {
                    Person changeAuthor;
                    if (bart.PersonId == bartHistory[i - 1].ChangeAuthorId)
                        changeAuthor = bart;
                    else
                        changeAuthor = context.People.Find(bartHistory[i - 1].ChangeAuthorId);

                    if (bartHistory[i - 1].Name != bartHistory[i].Name)
                        displayLog(bartHistory[i - 1].SysStartTime, changeAuthor, "Name", bartHistory[i].Name, bartHistory[i - 1].Name);

                    if (bartHistory[i - 1].Email != bartHistory[i].Email)
                        displayLog(bartHistory[i - 1].SysStartTime, changeAuthor, "Email", bartHistory[i].Email, bartHistory[i - 1].Email);

                    if (bartHistory[i - 1].Country != bartHistory[i].Country)
                        displayLog(bartHistory[i - 1].SysStartTime, changeAuthor, "Country", bartHistory[i].Country, bartHistory[i - 1].Country);
                }
            }
        }

        private static void displayLog(DateTime time, Person changeAuthor, string columnName, string oldValue, string newValue)
        {
            string changeAuthorName = changeAuthor != null ? changeAuthor.Name : "deleted user";
            Console.WriteLine($"{time}: User {changeAuthorName} changed {columnName} from {oldValue} to {newValue}");
        }
    }
}

E uscita del programma:

27.11.2016 16:20:31: User Bart Simpson changed Email from [email protected] to [email protected]
27.11.2016 16:16:12: User Homer Simpson changed Country from Scotland to USA
27.11.2016 16:16:04: User Homer Simpson changed Country from USA to Scotland
27.11.2016 16:15:14: User Homer Simpson changed Email from [email protected] to [email protected]