?
Solved

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

Posted on 2009-12-16
10
Medium Priority
?
157 Views
Last Modified: 2012-05-08
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
0
Comment
Question by:SamJolly
  • 5
  • 5
10 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26063494
you can implement the same methods of 2000 on 2005
 
0
 

Author Comment

by:SamJolly
ID: 26063634
Sorry, can you provide an example....

Thanks.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26063668
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()
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 

Author Comment

by:SamJolly
ID: 26063719
Thanks for this. What would the update trigger look like?

Thanks....
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 26063770
CREATE TRIGGER tr_u_SomeName
ON YourTableName
FOR UPDATE
AS
IF NOT UPDATE(Column1) OR NOT UPDATE(Col2)
BEGIN
RETURN
END
UPDATE y
SET dateModified = GETDATE()
FROM YourTableName y
JOIN inserted i on y.PrimaryKey = i.PrimaryKey
GO


Note : triggers can cause some performance issues
0
 

Author Comment

by:SamJolly
ID: 26064129
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

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26064167
>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
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26064186
0
 

Author Comment

by:SamJolly
ID: 26064284
Ok .... Thanks... Very helpful.

Sam
0
 

Author Closing Comment

by:SamJolly
ID: 31666808
Thx
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Screencast - Getting to Know the Pipeline

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question