Query to get updated column timestamp

Question

Hi All,

I have a table "TestCaseMaster" which consists of column "RunInTA" which is of datatype bit. I have added new column "DisableDate" which is of datatype datetime.
Whenever the value of RunInTA is updated, the timestamp has to be captured and updated in the column DisableDate.

For example: For TestCaseID 1, if RunInTA is updated from 1 to 0 on 7thJune2021 at 10.00.00 then the value of the column DisableDate for TestCaseID 1 should be "210607 10:00:00"

Please let me know what is the best way to this functionality in SQL.
Kindly waiting for your response.

Thanks,
Santosh

Answer 1

CREATE TABLE TestCaseMaster(TestCaseID int,RunInTA bit,DisableDate char(15))  
INSERT INTO TestCaseMaster VALUES(1,1,NULL)  
INSERT INTO TestCaseMaster VALUES(1,0,NULL)    
INSERT INTO TestCaseMaster VALUES(1,1,NULL)  
INSERT INTO TestCaseMaster VALUES(2,1,NULL)  
   
SELECT * FROM TestCaseMaster  
      
 --Create an Update trigger  
Create Trigger truTest  
 On TestCaseMaster   
 for Update   
 As   
 if Update(RunInTA)  
 begin  
 Update TestCaseMaster  
 Set DisableDate=FORMAT(GETDATE(),'yyMMdd hh:mm:ss')  
 From TestCaseMaster br, Deleted d, Inserted i   
 Where br.TestCaseID=d.TestCaseID   
  
end  
      
       
--Test: Check the changes of DisableDate after updating RunInTA  
UPDATE TestCaseMaster  
SET RunInTA=0  
FROM TestCaseMaster  
WHERE TestCaseID=1  
      
SELECT * FROM TestCaseMaster  
   
DROP Trigger truTest   
DROP TABLE TestCaseMaster  

Output:

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

For this you can use an UPDATE trigger with UPDATE function to test, which column was effected on the update.
See CREATE TRIGGER (Transact-SQL) and UPDATE - Trigger Functions (Transact-SQL) => Example

Answer 3

Hi @Santosh Umarani ,

Using triggers is a good choice, please refer to:

CREATE TABLE TestCaseMaster(TestCaseID int,RunInTA bit,DisableDate char(15))  
INSERT INTO TestCaseMaster VALUES(1,1,NULL)  
  
SELECT * FROM TestCaseMaster  
  
--Create an Update trigger  
Create Trigger truTest  
On TestCaseMaster   
for Update   
As   
if Update(RunInTA)  
begin  
Update TestCaseMaster  
Set DisableDate=FORMAT(GETDATE(),'yyMMdd hh:mm:ss')  
From TestCaseMaster br , Deleted d ,Inserted i   
end  
  
--Test: Check the changes of DisableDate after updating RunInTA  
UPDATE TestCaseMaster  
SET RunInTA=1  
FROM TestCaseMaster  
WHERE TestCaseID=1  
  
SELECT * FROM TestCaseMaster  
  
DROP TABLE TestCaseMaster  

Your expected output is in a format similar to "210607 10:00:00", so DisableDate needs to be set to char or varchar. If it is set to datetime, the final output format will be: 2021-06-07 05:26: 03.000.

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 4

Thank you Echo for the response. I have following two questions:

• If I create a trigger on a column and run update command for that column, the column value not get updated ?
  For example: When I run UPDATE [TestCaseMaster] SET RunInTA = '0' WHERE TestCaseID='1';
  DisableDate column is getting updated, however RunInTA is not getting updated in TestCaseMaster table.
• I want to update DisableDate column only when TestCaseID='1'. However, from the above query you have given, DisableDate column of all the rows of TestCaseMaster is getting updated.

Kindly waiting for your response.

Thanks,
Santosh