Trigger to set mod date for SQL 2005 table

Posted on 2007-10-01
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
    LVL 15

    Accepted Solution

    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

    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

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

    LVL 8

    Assisted Solution

    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

    Glad I could be of any help !

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Suggested Solutions

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    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 combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

    794 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now