Trigger to update a datetime field

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)

Trigger to update a datetime field

Author

Topic

KarenF
Starting Member

1 Post

Posted - 2004-03-11 : 15:03:18

I'm trying to write an insert/update trigger that will update a DATELASTMOD (datetime, 8) field with the current datetime info.

Could it be as simple as:
create trigger tr_datelastmod
on table1
for insert, update
as
begin
update table1
set datelastmod = getdate()
from table1 join inserted i
on table1.primarykey = i.primarykey
end

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-11 : 15:09:42

Yes that would work:



CREATE TABLE Table1
(
PrimaryKey INT,
datelastmod DATETIME
)

create trigger tr_datelastmod 
on table1
for insert, update
as
begin
update table1
set datelastmod = getdate()
from table1 join inserted i
on table1.primarykey = i.primarykey
end

INSERT INTO Table1 VALUES(1, 'Jan 2 2003')

SELECT * FROM Table1

DROP TABLE Table1

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-11 : 15:52:33

quote:

Originally posted by tduggan

Yes that would work:



CREATE TABLE Table1
(
PrimaryKey INT,
datelastmod DATETIME
)

create trigger tr_datelastmod 
on table1
for insert, update
as
begin
update table1
set datelastmod = getdate()
from table1 join inserted i
on table1.primarykey = i.primarykey
end

INSERT INTO Table1 VALUES(1, 'Jan 2 2003')

SELECT * FROM Table1

DROP TABLE Table1

Tara

I think if you use a default constraint on the table, you can eliminate any overhead on the inserts....

don't know how much...

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-11 : 16:30:35

Yes that would be the recommended approach.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources