How to update columns via SQL when a column has been modified? A bit like a newspaper! Microsoft SQL Server Management Studio


Okay, just to clarify: I have a SQL Table (contains ID, School, Student ID, Name, Fee $, Fee Type, and Paid (as the columns)) that needs to be posted on a Grid that will uploaded on a website. The Grid shows everything correctly and shows what Fees need to be Paid. The Paid column has a bit data type for 1 or 0 (basically a checklist.) I am being asked to add two more columns: User and DateChanged. The reason why is to log which staff changed the "Paid" column. It would only capture the Username of the staff who changed it in the SQL Table and also the time. So to clarify even more, I need to create 2 columns: "User, DateChanged" and the columns would log when someone changed the "Paid" column.

For example: User:Bob checks the Paid column for X student on 5/2/17 at 10pm.

In the same row of X student's info, under User column Tom would appear there. Under DateChanged it would show 2017-05-02 10pm.

What steps would I take to make this possible.

I'm currently IT Intern and all this SQL stuff is new to me. Let me know if you need more clarification. FYI The two new columns: User, DateChanged will not be on the grid.

The way to do this as you've described is to use a trigger. I have an example of some code below but be warned as triggers can have unexpected side-effects, depending on how the database and app interface are set up.

If it is possible for you to change the application code that sends SQL queries to the database instead, that would be much safer than using a trigger. You can still add the new fields, you would just be relying on the app to keep them updated instead of doing it all in SQL.

Things to keep in mind about this code:

  • If any background processes or procedures make updates to the table, it will overwrite the timestamp and username automatically, because this is triggered on any update to the row(s) in question.
  • If the users don't have any direct access to SQL Server (in other words, the app is the only thing connecting to the database), then it is possible that the app will only be using one database login username for everyone, and in that case you will not be able to figure out which user made the update unless you can change the application code.
  • If anyone changes something by accident and then changes it back, it will overwrite your timestamp and make it look like the wrong person made the update.
  • Triggers can potentially bog down the database system if there are a very large number of rows and/or a high number of updates being made to the table constantly, because the trigger code will be executed every time an update is made to a row in the table.

But if you don't have access to change the application code, and you want to give triggers a try, here's some example code that should do what you are needing:

create trigger TG_Payments_Update on Payments
after update

update Payments
set DateChanged = GetDate(), UserChanged = USER_NAME()
from Payments, inserted
where Payments.ID = inserted.ID