To read the article online, visit http://www.4GuysFromRolla.com/webtech/sqlguru/q051200-1.shtml

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


Article Information
Article Title: SQL Guru: UPDATE Trigger Not Working
Article Author: Scott Mitchell
Article URL: http://www.4GuysFromRolla.com/webtech/sqlguru/q051200-1.shtml


Copyright 2017 QuinStreet Inc. All Rights Reserved.
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers