Auto-increment value on UPDATE
-
Do you guys know of any easy way to have one column in a table of which the value auto updates every time an
UPDATEstatement 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 anUPDATEstatement 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 anUPDATEstatement 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 ofGetDate(). So for everyINSERTinto the table, the 'Inserted' column will automatically get the current date and time but I'd like to have something like this forUPDATEas 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)
ENDBut I'm hoping that there might be a more elegant solution.
-
Do you guys know of any easy way to have one column in a table of which the value auto updates every time an
UPDATEstatement 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 anUPDATEstatement 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 anUPDATEstatement 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 ofGetDate(). So for everyINSERTinto the table, the 'Inserted' column will automatically get the current date and time but I'd like to have something like this forUPDATEas 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)
ENDBut I'm hoping that there might be a more elegant solution.
This is an elegant solution, and is definitely your best bet here. Note that you should do an = test, not an IN test.
*pre-emptive celebratory nipple tassle jiggle* - Sean Ewington
"Mind bleach! Send me mind bleach!" - Nagy Vilmos
My blog | My articles | MoXAML PowerToys | Mole 2010 - debugging made easier - my favourite utility
-
Do you guys know of any easy way to have one column in a table of which the value auto updates every time an
UPDATEstatement 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 anUPDATEstatement 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 anUPDATEstatement 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 ofGetDate(). So for everyINSERTinto the table, the 'Inserted' column will automatically get the current date and time but I'd like to have something like this forUPDATEas 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)
ENDBut I'm hoping that there might be a more elegant solution.
I think that's the best approach. It should work for all updates. Second way I can think of is to create procedure for updating the table and using it for all updates, that occur. And inside this procedure you can also update your
versionandmodifiedcolumns. But that required you to stick with the procedures for all updates.Don't forget to rate answer, that helped you. It will allow other people find their answers faster.
-
This is an elegant solution, and is definitely your best bet here. Note that you should do an = test, not an IN test.
*pre-emptive celebratory nipple tassle jiggle* - Sean Ewington
"Mind bleach! Send me mind bleach!" - Nagy Vilmos
My blog | My articles | MoXAML PowerToys | Mole 2010 - debugging made easier - my favourite utility
-
Do you guys know of any easy way to have one column in a table of which the value auto updates every time an
UPDATEstatement 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 anUPDATEstatement 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 anUPDATEstatement 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 ofGetDate(). So for everyINSERTinto the table, the 'Inserted' column will automatically get the current date and time but I'd like to have something like this forUPDATEas 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)
ENDBut I'm hoping that there might be a more elegant solution.
-
Looks to be a good solution in my opinion. Just consider using an INNER JOIN instead of IN
UPDATE mytable Set Modified = GetDate() From myTable A Inner Join Inserted B Where A.RecordId = B.RecordId -
Do you guys know of any easy way to have one column in a table of which the value auto updates every time an
UPDATEstatement 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 anUPDATEstatement 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 anUPDATEstatement 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 ofGetDate(). So for everyINSERTinto the table, the 'Inserted' column will automatically get the current date and time but I'd like to have something like this forUPDATEas 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)
ENDBut I'm hoping that there might be a more elegant solution.
One thing to bear in mind: GETDATE isn't particularly accurate at the millisecond level. Although the datetime datatype is accurate to about 3 milliseconds, GETDATE does not give you that level of precision, as far as I can gather from a quick google it is somewhere around 15ms although things like machine load can affect it. If you are expecting to do repeated updates to the same row within a very short time period, this may not give you unique values for your Modified column. If your rows will not be updated that frequently, then it's not a problem. I thought SQL Server supported the ability to automatically timestamp rows when they were updated. The timestamp is just a meaningless number but at least you can check whether a row is the version you expect it to be, and you can also tell the order in which updates were applied. (I'm assuming SQL Server since you haven't said what database you are using.)