• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 199
  • Last Modified:

update date when field inserted or updated in SQL 2005

I have a table tblMessages with a field UpdateDate. right now the default value is (getdate()) to add the date when inserted. This does not however update when a row is updated.

What would be the best way to do this?
0
Shawn
Asked:
Shawn
  • 4
  • 4
  • 3
  • +1
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
You can create a trigger to do that, but i dot really recommend triggers for highly transactional systems..


0
 
ShawnAuthor Commented:
any other way than a trigger?
0
 
MohammedUCommented:
Either you can pass the UpdateDate as Getdate() when you are updating with your update script or
you can create an UPDATE trigger to modify the UpdateDate column.
http://msdn.microsoft.com/en-us/library/ms189799(SQL.90).aspx
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
chapmandewCommented:
triggers aren't necessarily bad for high trans systems...it just depends on how they are used.  if you have many small batches then a trigger should be fine.

create trigger triggername
on tablename
after insert, update
as
begin
update t
set datefield = getdate()
from tablename t
join inserted i on t.primarykey = i.primarykey
end
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
create trigger tr_u_tablename
on tablename
for update
as
  update tableName
  set datecolumn = Getdate()
  from TableName
  inner join inserted on tableName.columnName = inserted.columnName
go
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
or else you can include the datecolumn and update that to getdate() on all the table update statements
0
 
ShawnAuthor Commented:
right now I have the code below and I don't see any updating once I change a value in a field???
ALTER trigger [tr_u_tblMessages]
on [dbo].[tblMessages]
for update 
as 
  update tblMessages 
  set UpdateDate = Getdate() 
  from tblMessages 
  inner join inserted on tblMessages.UpdateDate = inserted.UpdateDate

Open in new window

0
 
chapmandewCommented:
nope...change this to be your primary key fields on your table:

inner join inserted on tblMessages.UpdateDate = inserted.UpdateDate

maybe?

inner join inserted on tblMessages.messageid= inserted.messageid
0
 
ShawnAuthor Commented:
I acutally tried the primary key firt and get this error message:
the row valu updated or deleted either do not make the row unique or they alter multiple rows

the trigger now looks like this:

ALTER trigger [tr_u_tblMessages]
on [dbo].[tblMessages]
for update 
as 
  update tblMessages 
  set UpdateDate = Getdate() 
  from tblMessages 
  inner join inserted on tblMessages.MessageID = inserted.MessageID

Open in new window

0
 
chapmandewCommented:
are you sure it is your primary key?

is your update modifying the primary key?
0
 
ShawnAuthor Commented:
I thought it was the primary key but just noticed the little key wasn't there.

It works now. thank you :)
0
 
chapmandewCommented:
ah hah!

welcome.  :)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now