• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 208
  • Last Modified:

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

Hi,

What is the best way to update a "UpdatedWhen" column with GetDate() in SqlServer. I had been using a constraint with AddedWhen

ALTER TABLE [dbo].[Table] ADD  CONSTRAINT [DF_Tables_AddedWhen]  DEFAULT (getdate()) FOR [AddedWhen]

But I do not think this works with an UPDATE, only an INSERT command.

Thoughts and some code qould be greatly appreciated,

Thanks,

Sam
0
SamJolly
Asked:
SamJolly
  • 5
  • 5
1 Solution
 
Pratima PharandeCommented:
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


0
 
SamJollyAuthor Commented:
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
0
 
Pratima PharandeCommented:
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
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
SamJollyAuthor Commented:
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?

0
 
Pratima PharandeCommented:
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
0
 
SamJollyAuthor Commented:
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

0
 
Pratima PharandeCommented:
try inserted


+++++++++++++++++++++
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 INSERTED U on T.ID = T.ID
END
GO
0
 
Pratima PharandeCommented:
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.
0
 
SamJollyAuthor Commented:
Hi,

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

Sam
0
 
SamJollyAuthor Commented:
thks, really appreciated
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now