Updating row with getdate() on update

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Updating row with getdate() on update

Author

Topic

mj
Starting Member

25 Posts

Posted - 2003-01-15 : 16:39:30

I'm trying to create a trigger that updates my "LastUpdated" column using a trigger:

Create Trigger UpdateMe on Estimates
for insert, update as
Update Estimates set LastUpdated = getdate()
where estimates.estimateID = inserted.estimateID

This is giving me an error saying that inserted does not match a table name used in the query.

I really appreciate anyones help on this one, thanks

mj

nr
SQLTeam MVY

12543 Posts

Posted - 2003-01-15 : 17:01:01

as it says

Create Trigger UpdateMe on Estimates for insert, update
as
Update Estimates
set LastUpdated = getdate()
from inserted
where estimates.estimateID = inserted.estimateID

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

afterburn
Starting Member

28 Posts

Posted - 2003-01-17 : 10:31:39

I beleive he is failing because the
for update,insert

This is because the An Update query uses the temp table of "deleted" and insert uses "Inserted"

So you would need to create 2 triggers one for insert another for update. Also because he has not from as your query has nr.

Create trigger tgrInsertTbl on tbl for
insert as

Update tbl set field = getDate() from
tbl inner join inserted on tbl.ID = inserted.ID
Go

Create trigger tgrUpdateTbl on tbl for
Update as

Update tbl set field = getDate() from
tbl inner join deleted on tbl.ID = deleted.ID

This will properly catch queries that affect more than one record.

Added query

Edited by - afterburn on 01/17/2003 10:34:59

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-01-17 : 10:46:13

quote:
Update tbl set field = getDate() from
tbl inner join deleted on tbl.ID = deleted.ID

Return an @@rowcount on that and let us know if it's ever > 1 ...
Don't forget ... these are after triggers ...

Jay White
{0}

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-17 : 10:52:30

quote:
This is because the An Update query uses the temp table of "deleted" and insert uses "Inserted"

An UPDATE trigger uses both the inserted and deleted pseudo tables. There's no need to have separate triggers for INSERT and UPDATE. The problem was that the original trigger did not have the proper FROM clause to join inserted to the original table:

CREATE TRIGGER UpdateMe ON Estimates
FOR INSERT, UPDATE AS
UPDATE Estimates SET LastUpdated = getdate()
FROM Estimates INNER JOIN inserted ON Estimates.estimateID = inserted.estimateID

This is essentially the same as Nigel's version.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources