Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    65

    Unanswered: How to make a trigger refer to the current record

    I have a table full of items that have a "date_updated" field. I'd like this field to be set to GETDATE() whenever a record is updated. I've got this trigger:

    CREATE trigger tr_cp_shiptos_u on dbo.cp_shiptos for update as
    update cp_shiptos set date_updated = GETDATE()

    Problem is, of course, there's no WHERE clause..yet. I don't know how to refer to the record that was updated.... for example:

    CREATE trigger tr_cp_shiptos_u on dbo.cp_shiptos for update as
    update cp_shiptos set date_updated = GETDATE()
    where shipto_id = @THIS_ID

    I imagine there's some kind of builtin variable or something like that. How is this done?

    Thanks in advance.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    CREATE TRIGGER tr_cp_shiptos_u ON dbo.cp_shiptos 
    FOR UPDATE
    AS
      BEGIN
    	UPDATE c 
    	   SET date_updated = GETDATE()
    	  FROM cp_shiptos c 
    	  JOIN inserted i
    	    ON i.key = c.key
      END
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2005
    Posts
    30
    That worked; thank you so much

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •