Back

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,
 CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED 
 ( [ID] ASC )
) ON [PRIMARY]

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
) ON [PRIMARY]

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
AFTER UPDATE
AS
BEGIN
	DECLARE @ColumnOrdinalTotal INT = 0;
	SELECT @ColumnOrdinalTotal = @ColumnOrdinalTotal 
        + POWER (
                2 
                , COLUMNPROPERTY(t.object_id,c.name,'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 s.name = 'dbo'
        AND t.name = 'Persons'
        AND c.name IN (
            'Firstname',
			'Lastname',
			'Email',
			'Phone',
			'Address',
			'ModifiedAt',
			'ModifiedBy'
        );

	IF (COLUMNS_UPDATED() & @ColumnOrdinalTotal) > 0
    BEGIN
        INSERT INTO dbo.Persons_Changes
        (
			dbo.Persons_Changes.TriggerID,
			dbo.Persons_Changes.Firstname_Old,
			dbo.Persons_Changes.Firstname_New,
			dbo.Persons_Changes.Lastname_Old,
			dbo.Persons_Changes.Lastname_New,
			dbo.Persons_Changes.Email_Old,
			dbo.Persons_Changes.Email_New,
			dbo.Persons_Changes.ModifiedBy,
			dbo.Persons_Changes.ModifiedAt
        )
        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;
    END
END
GO

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
    {
        [Column("ID")]
        public int Id { get; set; }
        [Column("Firstname")]
        public string? Firstname { get; set; }
        [Column("Lastname")]
        public string? Lastname { get; set; }
        [Column("Email")]
        public string? Email { get; set; }
        [Column("Phone")]
        public int? Phone { get; set; }
        [Column("Address")]
        public string? Address { get; set; }
        [Column("ModifiedAt")]
        public DateTime? ModifiedAt { get; set; }
        [Column("ModifiedBy")]
        public string? ModifiedBy { get; set; }
    }

Persons_Changes:

    [Table("Persons_Changes", Schema = "dbo")]
    internal class PersonChange
    {
        [Column("TriggerID")]
        public int Id { get; set; }
        [Column("Firstname_Old")]
        public string? FirstnameOld { get; set; }
        [Column("Firstname_New")]
        public string? FirstnameNew { get; set; }
        [Column("Lastname_Old")]
        public string? LastnameOld { get; set; }
        [Column("Lastname_New")]
        public string? LastnameNew { get; set; }
        [Column("Email_Old")]
        public string? EmailOld { get; set; }
        [Column("Email_New")]
        public string? EmailNew { get; set; }
        [Column("Phone_Old")]
        public int? PhoneOld { get; set; }
        [Column("Phone_New")]
        public int? PhoneNew { get; set; }
        [Column("Address_Old")]
        public string? AddressOld { get; set; }
        [Column("Address_New")]
        public string? AddressNew { get; set; }
        [Column("ModifiedAt")]
        public DateTime? ModifiedAt { get; set; }
        [Column("ModifiedBy")]
        public string? ModifiedBy { get; set; }
    }

DbContext:

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

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer("CONNECTION_STRING");
        }

        protected void OnModelCreation(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(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))
            .Distinct();
        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)
                {
                    changes.Add(
                        new Tuple<string, string?, string?>(
                            property, 
                            oldValue?.ToString(), 
                            newValue?.ToString()
                        ));
                }
            }
        }
        StringBuilder sb = new StringBuilder();
        foreach (var change in changes)
        {
            if (change.Equals(changes.Last()))
            {
                sb.Append($"{change.Item1} from '{change.Item2}' to '{change.Item3}'");
                break;
            }
            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)
        .AsNoTracking()
        .ToList();

   foreach (PersonChange change in personChanges)
    {
        Console.WriteLine(
            $"[{change.ModifiedAt}] {change.ModifiedBy} " +
            "updated the the properties " + 
            FormatChange<PersonChange>(change));
    }
}

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!