update table date/time - 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

General SQL Server Forums

New to SQL Server Programming

update table date/time

Author

Topic

josimafiosi
Starting Member

3 Posts

Posted - 2006-03-15 : 08:45:36

hi! i'm new to sql and working on my first database, I use microsoft sql server 2000 - in my table i want a row in which date and time is saved whenever the dataset is changed. I use getdate() as default value and it saves the time when I create a new entry, but not yet when I update an old one. I know that it must be a formula with something like update tbl set = getdate() but I don't know exactly how it works. any help on this?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-15 : 08:48:21

How do you update the table?
If you use Front End application, then update that date column to getdate()
Otherwise have a trigger on that table that updates datecolumn for each updation

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-15 : 08:48:34

you can use a update trigger to do this.

Something like this

create trigger trigger_name for update on table_name
as
begin
     update u
         set    last_update_date = getdate()
         from   inserted i inner join table_name u
         on     i.pk = u.pk
end

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-15 : 08:51:48

quote:

Originally posted by khtan

you can use a update trigger to do this.

Something like this

create trigger trigger_name for update on table_name
as
begin
     update u
         set    last_update_date = getdate()
         from   inserted i inner join table_name u
         on     i.pk = u.pk
end

with slight change

create trigger trigger_name on table_name  for update 
as
begin
     update u
         set    last_update_date = getdate()
         from   inserted i inner join table_name u
         on     i.pk = u.pk
end

Madhivanan

Failing to plan is Planning to fail

josimafiosi
Starting Member

3 Posts

Posted - 2006-03-15 : 08:52:44

the table is updated by a front end application, in my opinion getdate() should be the right function, I just can't get the code together to put in the 'formula' box

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-15 : 08:54:54

In the front end application's update statement add this ,datecol=getdate()

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-15 : 08:56:44

quote:

Originally posted by madhivanan

quote:

Originally posted by khtan

you can use a update trigger to do this.

Something like this

create trigger trigger_name for update on table_name
as
begin
     update u
         set    last_update_date = getdate()
         from   inserted i inner join table_name u
         on     i.pk = u.pk
end

with slight change

create trigger trigger_name on table_name  for update 
as
begin
     update u
         set    last_update_date = getdate()
         from   inserted i inner join table_name u
         on     i.pk = u.pk
end

Madhivanan

Failing to plan is Planning to fail

Oooops