-3

There is a table. Which has many column along with CreatedDateTime and LastModifiedDateTime. Whenever record got inserted CreatedDateTime Column should be updated with transaction date time(Current date time). Same way LastModifiedDateTime also should be updated when the Insertion and Update happen on a particular record.

This should be happen automatically without influence of Trigger and Default constraint.

If any other inbuilt solution is available?

EzLo
  • 13,780
  • 10
  • 33
  • 38
Arun Gunaekaran
  • 15
  • 3
  • I don't see why you wouldn't want to use the default constraint. It is the standard way of setting the create date. Do you see any disadvantage with it? – Nisarg Shah Apr 11 '18 at 06:50
  • You have not explained why you have your restrictions of not using default constraints or triggers. They are the standard methods. See this for additional ideas: https://stackoverflow.com/questions/21493178/need-a-datetime-field-in-ms-sql-that-automatically-updates-when-the-record-is-mo – RnP Apr 11 '18 at 06:56
  • I am just looking the option to perform in single and generic operation. apart from this. I mean like CDC. – Arun Gunaekaran Apr 11 '18 at 06:57
  • The reason for not using trigger is, I was enabled CDC on a table If I used UPDATE or INSERT trigger. It is performing After Insert and Before Insert Operation. Which means 2 records are created again. So that I am looking the option for it. And CreatedDateTime Should be updated Only once when the record is getting created. – Arun Gunaekaran Apr 11 '18 at 07:07

1 Answers1

0

CDC and triggers do not go hand by hand. Either you manage your CreatedDateTime and LastModifiedDateTime in your application (by explicitly inserting and updating accordingly), or you don't use CDC and use a trigger to track data changes and set default or modified values.

I believe that the CDC table functionality already tracks values like the datetime of each operation. It would be redundant to track that info as an actual column on the tracked table, unless you actually need it on your application for some business logic (in which case it would be wise to manage it on your client app).

EzLo
  • 13,780
  • 10
  • 33
  • 38