When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Stump the SQL Guru!
XML Info
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
The SQL Guru Answers your Questions...

Today's question comes from Julian.

I have a table with several fields including updated, year, updatedate as well as other tables. I am trying to create a trigger that fires upon insert and update to the table. What I want it to do is to put the current date and time (getdate()) into updatedate and year(getdate()) into year and set updated to 1.

I have tried various ways of doing this using an UPDATE SQL statement but this just updates every record in the table. I have tried to limit the records changed by using a WHERE TABLE.RECORDID = INSERTED.RECORDID but this just produces an error about inserted.recordid not existing. I tried an UPDATE statement on the INSERTED table but SQL complains that I can't update the INSERTED table.

How would I go about doing this? Thanks!


The problem is likely that you are not referring to the INSERTED table in your UPDATE Statement. I have found the following code is the simplest, most straightforward way to approach this:

CREATE TRIGGER CustomerUpdateTimeInserted
  ON Customer
 Update Customer set 
    UpdateDate = GetDate(),
    UpdateYear = Year(getdate())
 Where RecordID = (Select RecordID from Inserted)

This will work for INSERT or UPDATE


Read Other SQL Guru Questions

ASP.NET [1.x] [2.0] | ASPFAQs.com | Advertise | Feedback | Author an Article