SQL SERVER TIME UPDATE

I have an table "AOS" with "ETRTime" field and a "LastUpdate" (datetime field).    I need to capture the change event each time the "ETRTime"  is updated  in the l "LastUpdate" column - each time a change occurs.

Assistance would be greatly appreciated.
BOEING39Asked:
Who is Participating?
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
The trigger doesn't reference the column
Yes it does: CREATE TRIGGER ETRTimeChange
ON AOS.ETRTime
(...)
IF UPDATE (ETRTime)
(...)
0
 
Lokesh B RDeveloperCommented:
Hi,

You can use SqlDependency on the table to get notifications on each change.

https://msdn.microsoft.com/en-us/library/62xk7953(v=vs.110).aspx
0
 
BOEING39Author Commented:
So this code would be applied to the page where the update is made?
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You need to create a trigger for that and you need to have a PK on AOS. In this example I'm using ID as PK so you need to replace ID with the real PK column name:
CREATE TRIGGER ETRTimeChange
ON AOS.ETRTime
FOR UPDATE 
AS 
IF UPDATE (ETRTime) 
BEGIN
    UPDATE AOS
    SET LastUpdate=GETDATE()
    FROM AOS 
    INNER JOIN inserted ON AOS.ID = inserted.ID
END;

Open in new window

0
 
BOEING39Author Commented:
Thx for the response.     This makes sense; however, I do have a question.    The trigger doesn't reference the column I need to update based on a change in "ETRTime" namely "LastUpdate".    I need to capture each instance when the time is updated.
0
 
BOEING39Author Commented:
Sorry for the late response.    That work for me.    Thanks again for the prompt responses.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.