SQL Server – create trigger to log changes in DB

About 6 months ago I wrote about the way to create a log sheet in Excel with VBA.

Nowadays, as far as I am doing a small upgrade from MS Excel to MS SQL Server 🙂 , I was interested to find a way to repeat the exercise. Thus, with the power of SQL Server, there was no doubt that a such task is possible.


How to do it?

First, create a table where to log the changes. Then, generate a trigger with SQL. Now, let’s imagine that we want to build a table, that follows the changes of the Money table from this example.

Pretty much, this is how the empty table with logs would look like:


We have columns Id, ChangeDate, Command and UserName, which would be filled out automatically on a change. The other columns are OldCell, NewCell, NewPaidPerCell and OldPaidPerCell. They would be filled out in case of update, delete or insert is carried out (also automatically btw) :).

Thus, this is what you will get in the log table, after doing 2 delete commands, one update and one insert. The NULL values are present, because we do not have oldValues on insert neither newValues on Delete.


How do you generate this magic 🙂 ?

Pretty much, 45 lines of SQL are quite enough.

Here comes the code:

Enjoy it! 😀

Tagged with: , , , ,