CODEWITHSUNDEEP
sqlsql-server-2005datetimeinsert
steventnorris
steventnorris

Reputation: 5896

On Update, change date

I am using SqlServer 2005. I would like to set a property (like default value, but for any change, not just inserts) that would update a date. In other words, on every update and insert, the current date would be placed in the column. How would this be achieved?

Upvotes: 0

Views: 66

Answers (2)

ShyJ
ShyJ

Reputation: 4640

Let's suppose you have an A table:

CREATE TABLE A (
  ID INT,
  Name VARCHAR(20),
  SomeDate DATE NULL)

You can create triggers:

CREATE TRIGGER INSTEAD_OF_A_INSERT
ON A
INSTEAD OF INSERT AS
BEGIN

INSERT INTO A (ID, Name, SomeDate)
SELECT ID, Name, GETDATE ()
FROM inserted 

END
GO

CREATE TRIGGER INSTEAD_OF_A_UPDATE
ON A
INSTEAD OF UPDATE AS
BEGIN

UPDATE A
SET SomeDate = GETDATE(),
    Name = I.Name
FROM A A1 JOIN INSERTED I ON A1.ID = I.ID

END
GO

Which will give what you want

INSERT INTO A (ID, Name)
VALUES(1, 'John');

INSERT INTO A (ID, Name)
VALUES(2, 'Jack');

UPDATE A
SET Name = 'Jane'

    SELECT *
    FROM A

Here is a fiddle you can play with.

Upvotes: 1

Jon Egerton
Jon Egerton

Reputation: 41549

The easiest (but not necessarily most efficient) way to do this would be with a trigger.

See MSDN.

You could either use an AFTER INSERT,UPDATE or an INSTEAD OF INSERT,UPDATE - either could be made to do the job depending on your exact requirements.

Upvotes: 0

Related Questions