Hi,

I added a column with name ModifiedOn and set default value as Getdate() when a new record was added i can see the value but i want to know the last modified date when any record was modified.

How to achieve this ? Please help.

You can do this in two ways.

1) CDC

you can enable change data capture and get the information all the changes.

2)Trigger: You can write a trigger to update the column "name ModifiedOn and set default value as Getdate() "

For DML operations.

• Proposed as answer by Naomi NEditor 3 hours 7 minutes ago

Writer a "on update" DML trigger to update your column ModifiedOn.

Hi AngaraKiran,

The default constraint only works when inserting. To update that column last modified date you may have to either rely on a trigger after update.

CREATE TABLE TBL
	(
	 ID INT PRIMARY KEY, 
	 modifiedOn DATETIME DEFAULT(GETDATE())
	 );
GO

INSERT INTO TBL(ID) VALUES(1);
INSERT INTO TBL(ID) VALUES(2);
SELECT * FROM TBL;
GO

--Created a TRIGGER AFTER UPDATE
CREATE TRIGGER TBL_UA ON TBL
AFTER UPDATE
AS 
UPDATE TBL
SET     modifiedOn = DEFAULT
WHERE ID IN (SELECT inserted.ID FROM inserted)
GO

UPDATE TBL SET modifiedOn=DEFAULT
SELECT * FROM TBL;

Pay attention that a trigger does add overhead, especially when it triggers frequently. There's some other options, such as concatenating "modifiedOn=DEFAULT" to your update statement, I don't know how your statement is generated. However if you can take control of generating it, concatenating the forementioned part wold be the best practice.

If you have any question, feel free to let me know.