Update time in table - 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

Transact-SQL (2000)

Update time in table

Author

Topic

Karander
Starting Member

34 Posts

Posted - 2006-04-03 : 11:31:42

Hi,

I have to add to a table field that i could store
date time of record which was inserted or updated.

I think there are two ways (But I don't know how to do it)

First one:
- create table and add date/time field with default value (system function) if it is possible

- create trigger which could update date/time field

First option i think would be best, but how to do it ??

Thank you for an answer

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-04-03 : 11:38:02

default value won't execute on update. for that you need a trigger.
if you have your access through stored procedure you can do the update there.
otherwise do it in the trigger.

Go with the flow & have fun! Else fight the flow

Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

Karander
Starting Member

34 Posts

Posted - 2006-04-03 : 11:49:05

Could you write me an example of that trugger on insert/update event?

Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-04-03 : 11:57:45

create trigger trigYourTable
on YourTable
for insert, update
as
update t1
set LastUpdated = getdate()
from YourTable t1 join inserted i on t1.id = i.id

Go with the flow & have fun! Else fight the flow

Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

Karander
Starting Member

34 Posts

Posted - 2006-04-03 : 12:08:59

Really Thanks,

it works brilliantly, thx ;]

Another question is, is Trigger fired when i insert record with my date/time (In sql statement) ?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-04-03 : 12:12:17

what do you mean?

a trigger is executed for every update or insert you make.
it doesn't execute for each row but for all rows inserted/updated just once.

look for "create trigger" in BOL = books online = sql server help

Go with the flow & have fun! Else fight the flow

Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources