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.

sqlserver

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:

chess1

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.

chess2

How do you generate this magic 🙂 ?

Pretty much, 45 lines of SQL are quite enough.

Here comes the code:

Enjoy it! 😀

About

VBA Developer

Tagged with: , , , ,