asked on

Creating an Update trigger in SQL Server 2005 to update DateModified field.....

Hi,

Can you suggest a good way to automatically update a "DateModified" field in SQL Server 2005 with the current date/time. I have checked online and current methods seem a bit convoluted. I believe that SQL Server 2000 was trickier with this issue....

So thoughts and even some code appreciated,

Thanks,

Sam

Aneesh

you can implement the same methods of 2000 on 2005

SamJolly

ASKER

Sorry, can you provide an example....

Thanks.

Aneesh

you can implement it using a trigger (which i wont personnaly prefer ) or can do it whenever you do an insert / update operation on that table
for example
insert into youtable (col1, col2, datemodified)
select valu1, value2, getdate()

SamJolly

ASKER

Thanks for this. What would the update trigger look like?

Thanks....

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SamJolly

ASKER

Thanks for this... Some questions..

1) IF NOT UPDATE(Column1) OR NOT UPDATE(Col2)
BEGIN
RETURN
END

What is this about, Columns1, col2 ????

2) JOIN inserted i on y.PrimaryKey = i.PrimaryKey

What is this about? "inserted"....????

Thanks,

Sam

Aneesh

>UPDATE(Column1) OR NOT UPDATE(Col2)

in case you did some update on clolumn1, then UPDATE(colun1) will be true

you have to put the proper column names there ... i have no idea what your table look like

>2
inserted / deleted are called magic tables which can only be accessed within a trigger

Aneesh

http://www.devarticles.com/c/a/SQL-Server/Using-Triggers-In-MS-SQL-Server/

SamJolly

ASKER

Ok .... Thanks... Very helpful.

Sam

SamJolly

ASKER

Thx