Trigger to set mod date for SQL 2005 table

Posted on 2007-10-01
Medium Priority
Last Modified: 2008-03-06
I would like to write a SQL 2005 trigger to set the value of a last mod date column in any row that is inserted or updated in a specific table. Is this possible? I can create a trigger, but I don't know how to change the data for the inserted/updated row.
Question by:Paracom_Inc
  • 3
LVL 15

Accepted Solution

dbbishop earned 900 total points
ID: 19993362
CREATE TRIGGER dbo.updateTimeSatmp ON dbo.myTable FOR INSERT, UPDATE AS
UPDATE myTable
SET LastUpdated = GETDATE()
FROM myTable t1 INNER JOIN inserted t2 ON t1.PKcolumn = t2.PKColumn
LVL 18

Assisted Solution

Yveau earned 900 total points
ID: 19993391
when you have a column 'ModDate' it can be done by the trigger:

create trigger MyTrigger on Mytable
for update
    update table Mytable
    set ModDate = getdate
    where inserted.ID = Mytable.ID -- match the record(s) on the PK fields

hope this helps ...
LVL 18

Expert Comment

ID: 19993399
sorry, typo !
... getdate is a function ofcourse, so you need 'getdate()'


Assisted Solution

MrRobot earned 200 total points
ID: 19993801
for insertion, you can give the default value of "getdate()", so when a new row is added, the field will take the date value automatically.
LVL 18

Expert Comment

ID: 19996276
Glad I could be of any help !

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

840 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