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