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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
CREATE TABLE MoneyLogs ( Id INT PRIMARY KEY IDENTITY(1,1) NOT NULL, ChangeDate DATETIME DEFAULT GETDATE() NOT NULL, Command NCHAR(6) NOT NULL, OldCell BIGINT NULL, NewCell BIGINT NULL, OldPaidPerCell BIGINT NULL, NewPaidPerCell BIGINT NULL, UserName NCHAR(100) NOT NULL ) GO CREATE TRIGGER money_chess_change ON Money AFTER INSERT, UPDATE, DELETE AS BEGIN DECLARE @operation CHAR(6) SET @operation = CASE WHEN EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted) THEN 'Update' WHEN EXISTS(SELECT * FROM inserted) THEN 'Insert' WHEN EXISTS(SELECT * FROM deleted) THEN 'Delete' ELSE NULL END IF @operation = 'Delete' INSERT INTO MoneyLogs (Command, ChangeDate, OldCell, OldPaidPerCell, UserName) SELECT @operation, GETDATE(), d.Cell, d.PaidPerCell, USER_Name() FROM deleted d IF @operation = 'Insert' INSERT INTO MoneyLogs (Command, ChangeDate, NewCell, NewPaidPerCell, UserName) SELECT @operation, GETDATE(), i.Cell, i.PaidPerCell, USER_Name() FROM inserted i IF @operation = 'Update' INSERT INTO MoneyLogs (Command, ChangeDate, NewCell, OldCell, NewPaidPerCell,OldPaidPerCell, UserName) SELECT @operation, GETDATE(), d.Cell, i.Cell, d.PaidPerCell, i.PaidPerCell, USER_Name() FROM deleted d, inserted i END GO |
Enjoy it! 😀