Modified_Date Trigger - 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

Modified_Date Trigger

Author

Topic

siportal
Starting Member

5 Posts

Posted - 2008-05-22 : 19:46:05

I would like to create a trigger that uses GetDate() to automatically insert the date into the Modified_Date field in a table. Seems simple, but I can't figure out how.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-22 : 20:03:51

is the Modified_Date column in the same table that you want to put the trigger on or is it an "audit table"?
If the table is always written to via a stored procedure then it would be better to incorporate the logic in the SP.

PLEASE read up on triggers in Books Online

Assuming it is the same table here is the basic idea:


create table myTable (pk int identity, i int, mod_date datetime null)
go

create trigger tr_myTable_insUpd on myTable after insert, update
as
begin
	update mt set
		mod_date = getdate()
	from	inserted i
	inner join myTable mt on mt.pk = i.pk
end
go

insert myTable (i)
select i
from	(
	select 10 i union all
	select 20 union all
	select 30
	) d
order by i
waitfor delay '00:00:01.000'
update myTable set
	i = 40 where pk = 2

select * from myTable
go
drop table myTable

output:
pk          i           mod_date                                               
----------- ----------- ------------------------------------------------------ 
1           10          2008-05-22 20:00:08.810 
2           40          2008-05-22 20:00:09.843 
3           30          2008-05-22 20:00:08.810

Be One with the Optimizer
TG

siportal
Starting Member

5 Posts

Posted - 2008-05-23 : 07:54:28

Thanks your example worked. I don't have an audit table. I modified your sql and this worked for me.

create trigger tr_myTABLE_mod_date on dbo.[myTABLE] after insert, update
as
begin
update T set
mod_date = getdate()
from inserted i
inner join dbo.[myTABLE] T on T.PRIMARYKEY = i.PRIMARYKEY
end
go

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources