SQL save changes w. trigger

Feb 3, 2024 min read

The other day at work, I had to work on an old solution (some C# web API with an SQL) that a colleague had created a long time ago. Part of this solution was to create a kind of version history of objects and who had made the individual changes.

As I opened up the project, I saw some of the wildest code (in my opinion) I’ve seen in a long time to solve an otherwise simple problem๐Ÿ˜ฎ. Without going too deep into what was done, I can say that the person who created it was very fond of reflection in C#.

How would I have done it?

Since I’ve worked a lot with SQL, it would be obvious for me to look at how to solve this directly in the database, rather than having to create a lot of generic C# code that almost no one would be able to read afterwards. So the obvious choice would be to create an SQL trigger on the exact table that should have a version history.

So let’s take an example

Let’s say we have a Person table like this:

CREATE TABLE [dbo].[Persons](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Firstname] [nvarchar](255) NOT NULL,
	[Lastname] [nvarchar](255) NOT NULL,
	[Email] [nvarchar](255) NULL,
	[Phone] [int] NULL,
	[Address] [nvarchar](255) NULL,
	[ModifiedAt] [datetime] NULL,
	[ModifiedBy] [nvarchar](255) NULL,
 ( [ID] ASC )

From this table, we want to log every time a change occurs, not just that a change happens, but also what individual value changes from and to. This should, of course, be stored in a table as well, and such a table could look like this:

CREATE TABLE [dbo].[Persons_Changes](
	[TriggerID] [int] NOT NULL,
	[Firstname_Old] [nvarchar](255) NULL,
	[Firstname_New] [nvarchar](255) NULL,
	[Lastname_Old] [nvarchar](255) NULL,
	[Lastname_New] [nvarchar](255) NULL,
	[Email_Old] [nvarchar](255) NULL,
	[Email_New] [nvarchar](255) NULL,
	[Phone_Old] [int] NULL,
	[Phone_New] [int] NULL,
	[Address_Old] [nvarchar](255) NULL,
	[Address_New] [nvarchar](255) NULL,
	[ModifiedBy] [nvarchar](255) NULL,
	[ModifiedAt] [datetime] NULL

Now for the fun part๐ŸŽ‰ - namely, the logic that triggers each time a row in the Person table is updated. The trigger is designed to only save the values that are actually changed; the others that remain unchanged are simply set to NULL.

Trigger code

CREATE TRIGGER TriggerPerson_AfterUpdate
ON dbo.Persons
	DECLARE @ColumnOrdinalTotal INT = 0;
	SELECT @ColumnOrdinalTotal = @ColumnOrdinalTotal 
        + POWER (
                , COLUMNPROPERTY(t.object_id,,'ColumnID') - 1
    FROM sys.schemas s
        INNER JOIN sys.tables t ON s.schema_id = t.schema_id
        INNER JOIN sys.columns c ON t.object_id = c.object_id
    WHERE = 'dbo'
        AND = 'Persons'
        AND IN (

	IF (COLUMNS_UPDATED() & @ColumnOrdinalTotal) > 0
        INSERT INTO dbo.Persons_Changes
        SELECT d.ID
            , CASE WHEN d.Firstname = i.Firstname THEN NULL ELSE d.Firstname END
			, CASE WHEN d.Firstname = i.Firstname THEN NULL ELSE i.Firstname END
			, CASE WHEN d.Lastname = i.Lastname THEN NULL ELSE d.Lastname END
			, CASE WHEN d.Lastname = i.Lastname THEN NULL ELSE i.Lastname END
			, CASE WHEN d.Email = i.Email THEN NULL ELSE d.Email END
			, CASE WHEN d.Email = i.Email THEN NULL ELSE i.Email END
			, i.ModifiedBy
			, i.ModifiedAt
        FROM inserted i 
            LEFT JOIN deleted d ON i.ID = d.ID;

Now there should be a row like these every time a Person is updated

TriggerID Firstname_Old Firstname_New Lastname_Old Lastname_New ModifiedBy ModifiedAt
1 Jeppe1 Jeppe NULL NULL JSC 2024-02-03 11:30:19
1 NULL NULL Spanggaard Christensen JSC 2024-02-03 11:35:23

And now just for fun - how can this be used in C#? First of all, we need to create the DTO classes, one for the Persons table and one for Persons_Changes. Unlike many, I hardly ever use scaffold with Entity Framework; I create the models myself.

Persons class:

    [Table("Persons", Schema = "dbo")]
    internal class Person
        public int Id { get; set; }
        public string? Firstname { get; set; }
        public string? Lastname { get; set; }
        public string? Email { get; set; }
        public int? Phone { get; set; }
        public string? Address { get; set; }
        public DateTime? ModifiedAt { get; set; }
        public string? ModifiedBy { get; set; }


    [Table("Persons_Changes", Schema = "dbo")]
    internal class PersonChange
        public int Id { get; set; }
        public string? FirstnameOld { get; set; }
        public string? FirstnameNew { get; set; }
        public string? LastnameOld { get; set; }
        public string? LastnameNew { get; set; }
        public string? EmailOld { get; set; }
        public string? EmailNew { get; set; }
        public int? PhoneOld { get; set; }
        public int? PhoneNew { get; set; }
        public string? AddressOld { get; set; }
        public string? AddressNew { get; set; }
        public DateTime? ModifiedAt { get; set; }
        public string? ModifiedBy { get; set; }


    internal class TestDbContext : DbContext
        public DbSet<Person> Persons { get; set; }
        public DbSet<PersonChange> PersonChanges { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)

        protected void OnModelCreation(ModelBuilder modelBuilder)

Once we have this in place, we can start playing with the data in C#, and for fun, I’m thinking of creating a ’nice’ display of who and when the individual values were changed. And even though I started joking about reflection, I use it myself (when it makes sense)๐Ÿ˜œ.

internal static string FormatChange<T>(T changeObj)
        List<Tuple<string,string?,string?>> changes = new List<Tuple<string, string?, string?>>();
        var properties = typeof(T).GetProperties();
        var filteredProperties = properties
            .Where(x => x.Name.Contains("Old") || x.Name.Contains("New"));
        var distinctProperties = filteredProperties
            .Select(x => x.Name.Substring(0, x.Name.Length - 3))
        foreach (var property in distinctProperties)
            var oldValueProperty = typeof(T).GetProperty($"{property}Old");
            var newValueProperty = typeof(T).GetProperty($"{property}New");
            if (oldValueProperty != null && newValueProperty != null)
                var oldValue = oldValueProperty.GetValue(changeObj);
                var newValue = newValueProperty.GetValue(changeObj);
                if (oldValue != null && newValue != null)
                        new Tuple<string, string?, string?>(
        StringBuilder sb = new StringBuilder();
        foreach (var change in changes)
            if (change.Equals(changes.Last()))
                sb.Append($"{change.Item1} from '{change.Item2}' to '{change.Item3}'");
            sb.Append($"{change.Item1} from '{change.Item2}' to '{change.Item3}' and ");
        return sb.ToString();

When we bring it all together in a test application like this:

static void Main(string[] args)
    Console.WriteLine("Hello, World!");
    TestDbContext dbContext = new TestDbContext();

    List<Person> persons = dbContext.Persons.ToList();
    Person person1 = persons.First();

    List<PersonChange> personChanges = dbContext.PersonChanges
        .Where(x => x.Id == person1.Id)
        .OrderBy(x => x.ModifiedAt)

   foreach (PersonChange change in personChanges)
            $"[{change.ModifiedAt}] {change.ModifiedBy} " +
            "updated the the properties " + 

We get this output:

[2024-02-03 11:30:19] JSC updated the the properties Firstname from 'Jeppe1' to 'Jeppe'
[2024-02-03 11:35:23] JSC updated the the properties Lastname from 'Spanggaard' to 'Christensen'

Is it then a better solution?

I can’t answer that, but for me, it is - and that’s because I believe something like this belongs in a database and not in code. Also, for me, it’s much easier to read a trigger than a bunch of reflection code๐Ÿ˜.

Jeppe Spanggaard

A passionate software developer. I love to build software that makes a difference!