UPDATE Trigger is updating all the table!

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

General SQL Server Forums

New to SQL Server Programming

UPDATE Trigger is updating all the table!

Author

Topic

amarundo
Starting Member

9 Posts

Posted - 2012-02-07 : 13:54:25

Hi,

I'm a newbie...

I created the following trigger:

[StampUpdate_TMP_TEST_LIST] on [dbo].[TMP_TEST_LIST]
AFTER UPDATE AS
UPDATE TMP_TEST_LIST
SET RecordUpdatedOn = getdate()

Because I want to know when was the last time a record was updated.
The problem is that it's updating the whole table!!!

Of course I know that generally speaking you need a WHERE clause, but I thought that a trigger would be different.

The question is, how do I make the trigger update the RecordUpdatedOn column just for the record being updated? How can I give it a WHERE clause if I don't know how the UPDATE is happening?

Thanks!

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-07 : 13:56:01

UPDATE t
SET RecordUpdatedOn = getdate()
FROM TMP_TEST_LIST t
JOIN inserted i
ON t.key = i.key

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

amarundo
Starting Member

9 Posts

Posted - 2012-02-08 : 12:02:42

Hi Brett,

It's giving me an error. I'm running this script:

ALTER TRIGGER
[StampUpdate_TMP_TEST_CALL_LIST] on [dbo].[TMP_TEST_CALL_LIST]
AFTER UPDATE AS
UPDATE ttt
SET RecordUpdatedOn = getdate()
FROM [dbo].[TMP_TEST_CALL_LIST] ttt
JOIN inserted iii
ON ttt.key = iii.key

And the error is
Msg 156, Level 15, State 1, Procedure StampUpdate_TMP_TEST_CALL_LIST, Line 8
Incorrect syntax near the keyword 'key'.

This is MS SQL 2005.

Thanks!!!

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-08 : 12:07:50

I'm sorry

What is the primary key of your table TMP_TEST_CALL_LIST?

What makes it unique? What's that column(s) name(s)???

That replaces the word key

Does it have a primary key or unique index or something you "think" is unique?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

amarundo
Starting Member

9 Posts

Posted - 2012-02-09 : 09:58:20

oh, got it. I thought "key" was something internal in SQL. Yes, I do have a column that uniquely identified the record.
Will test. Thanks.

(I told you I'm a newbie...)

amarundo
Starting Member

9 Posts

Posted - 2012-02-09 : 11:55:47

Hey Brett,
I worked (something tells me you are not surprised...)
Thanks for your patience, man.
- A

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources