Last Modified field - SQL Server Forums

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)

Last Modified field

Author

Topic

hueby
Posting Yak Master

127 Posts

Posted - 2005-04-21 : 17:34:29

Hi all, I have a table that has a "Last Modified" column within it. I want to capture the date/time when a SINGLE row within that table is modified from it's original value.

I thought a trigger would do this. Currently I have this trigger in place:

create trigger lastmod3 on Hours for update
as
update Hours
set LastMod3 = getdate()
from Hours

This takes the "Last Modified" column named lastmod3, from the Hours table, and get's the date/time when it's updated.

But In the table under the lastmod3 column all the rows have the same most current date/time. So, it's not working for EACH row.

Does this make sense, and what can I do?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-04-21 : 17:38:24

You update statement has no selection criteria, so it will update every row in the table.

CODO ERGO SUM

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-04-21 : 17:39:06

Aaaah,
update Hours
set LastMod3 = getdate()
from Hours join inserted on Hours.key = inserted.key

At the moment You are updating the whole table for each update.

rockmoose

hueby
Posting Yak Master

127 Posts

Posted - 2005-04-21 : 17:44:39

Okay, I understand that. Now, can you explain some more about this line of code:
from Hours join inserted on Hours.key = inserted.key

I'm not exactly sure what it is telling the trigger to do, and what I need to replace (ie: hours.key, inserted.key) Whats going on here? Thanks guys!

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-04-21 : 17:58:51

In a trigger there are temporary/local tables that holds a copy of the rows that were affected in the "real" table.
(Actually there are 2, and they are called "inserted" and "deleted")

So we want to update the LastMod3 in the Hours for all the rows that were affected by the update;
To do this we use the temporary/local inserted table which holds a copy of these rows.
We join Hours with inserted on their key(s) (What is the primary key of Hours table?)

It pretty much says if You read through the CREATE TRIGGER documentation in BOL.

rockmoose

hueby
Posting Yak Master

127 Posts

Posted - 2005-04-21 : 18:13:25

Ohhh okay that is more clear to me now. Thank you, I will look up CREATE TIGGER In the BOL and read . I replaced the .key with my primary key, and looks like it is working better now. Thanks

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-04-21 : 18:18:07

You are welcome.
There is a topic in bol "Using the inserted and deleted Tables"
You can find it in the index "inserted tables" + "deleted tables"

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources