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

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
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!

Julian

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
  FOR UPDATE
AS
 Update Customer set 
    UpdateDate = GetDate(),
    UpdateYear = Year(getdate())
 Where RecordID = (Select RecordID from Inserted)

This will work for INSERT or UPDATE

Tom


Read Other SQL Guru Questions


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