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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BOEING39Author Commented:
Sorry for the late response.    That work for me.    Thanks again for the prompt responses.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.