Best way to automatically update an "UpdatedWhen" column on a table, Trigger or Contraint??

you need to add trigger on update
change the EMPid with you field name


CREATE TRIGGER myTrigger
    ON Employee
    FOR  UPDATE
 AS
   
    BEGIN
     Update T

      set T.[AddedWhen] = getdate()

     From [Table] T inner join Updated U on T.EmpID = T.EmpID
 END
 GO

Thanks for this . OK Trigger is best. Actually I wanted code for updating the column on the table that was being updated, so something like:

CREATE TRIGGER dbo.DT_MyTable  ON  dbo.MyTable
   AFTER UPDATE
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for trigger here
 
                   UpdatedWhen = GetDate();   -- This is incorrect.. so what should go here???
END
GO


Thanks,

Sam

CREATE TRIGGER dbo.DT_MyTable  ON  dbo.MyTable
   AFTER UPDATE
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for trigger here
 
                  Update T

      set T.[AddedWhen] = getdate()

     From [Table] T inner join Updated U on T.EmpID = T.EmpID
END
GO



change the EMPid with you field name

Hi,

Thanks but, I think we are talking at cross purposes here. I do not want to update "another" T table. I just want to update "MyTable" that the trigger belongs to. So basically when a record of "MyTable" is updated then the database should automatically "UPDATE" that record's "UpdatedWhen" column with "GetDate()". This is what I need.

Thoughts?

correct

this staemet update ypuir table only , give your table name in [Table] and updated is the table which is temporary table created by SQL which contain data of updated record



       Update T

      set T.[AddedWhen] = getdate()

     From [Table] T inner join Updated U on T.EmpID = T.EmpID

Hi,

This is where I am at now... still not quite working.

+++++++++++++++++++++
CREATE TRIGGER DT_MyTable_UpdatedWhen ON dbo.MyTable
   FOR UPDATE
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for trigger here
 
    Update T
    set T.[UpdatedWhen] = getdate()
      From dbo.MyTables T inner join Updated U on T.ID = T.ID
END
GO
+++++++++++++++++++++++++

I get a runtime error on "Updated".... unknown object. Also this line:

"From dbo.MyTables T inner join Updated U on T.ID = T.ID" looks odd, certainly "T.ID = T.ID". Should it be "U.ID = T.ID"?

How does the trigger find the record that is being updated?

Thanks for your help this far.

Sam

deleted and inserted are logical (conceptual) tables. They are structurally similar to the table on which the trigger is defined, that is, the table on which the user action is attempted, and hold the old values or new values of the rows that may be changed by the user action.

Hi,

That works... This is weird since I am not Inserting a record. You would think that there was a "UPDATED" logical table.

Sam

thks, really appreciated