C# Programming
02-01-2012, 01:20 PM
Do you guys know of any easy way to have one column in a table of which the value auto updates every time an UPDATE statement is called for a specific row?
Either one of the following two scenarios would be fine but I'm not sure what the easiest way would be of implementing it:
1. Let's say there's a column in the table called 'Version' (int). Every time an UPDATE statement is called, the value of the 'Version' column is automatically incremented by 1 for every row affected.
2. Let's say there's a column in the table called 'Modified' (datetime). Every time an UPDATE statement is called, the value of the 'Modified' column is automatically set to the current date and time. I have a similar concept to this in my DB with a column named 'Inserted' which has a default value of GetDate(). So for every INSERT into the table, the 'Inserted' column will automatically get the current date and time but I'd like to have something like this for UPDATE as well.
Any ideas? My best idea so far would be to create a trigger on the table, something as follows:
CREATE TRIGGER update_mytable ON MyTable FOR UPDATE AS BEGIN UPDATE MyTable SET Modified = GETDATE() WHERE RecordID IN (SELECT RecordID FROM INSERTED) END
But I'm hoping that there might be a more elegant solution.
Either one of the following two scenarios would be fine but I'm not sure what the easiest way would be of implementing it:
1. Let's say there's a column in the table called 'Version' (int). Every time an UPDATE statement is called, the value of the 'Version' column is automatically incremented by 1 for every row affected.
2. Let's say there's a column in the table called 'Modified' (datetime). Every time an UPDATE statement is called, the value of the 'Modified' column is automatically set to the current date and time. I have a similar concept to this in my DB with a column named 'Inserted' which has a default value of GetDate(). So for every INSERT into the table, the 'Inserted' column will automatically get the current date and time but I'd like to have something like this for UPDATE as well.
Any ideas? My best idea so far would be to create a trigger on the table, something as follows:
CREATE TRIGGER update_mytable ON MyTable FOR UPDATE AS BEGIN UPDATE MyTable SET Modified = GETDATE() WHERE RecordID IN (SELECT RecordID FROM INSERTED) END
But I'm hoping that there might be a more elegant solution.