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
