Menu
Our customer wants to order by the record creation date. To me this seems like a system variable, some sort of meta data for the record itself.
Is there a way to tell when a record was created without actually creating a datetime field with a default of GetDate() and hope that no one modifies it?
487
If you want to know when a row is inserted, the easiest thing would be to simply add a date or timestamp field with a default value (like getDate()) that automatically fills in the date/time when the row is inserted.
To get the last updated record in SQL Server: We can write trigger (which automatically fires) i.e. whenever there is a change (update) that occurs on a row, the “lastupdatedby” column value should get updated by the current timestamp.
Right click on the table you want to track changes. Click Properties, click Change Tracking, then in the right pane set Change Tracking to TRUE.
Nope.
You need to have a column for this.
Imagine how big the meta-data would be if you needed to keep a record for each record for creation! Would you also want to keep meta-data on your meta-data so you know when the meta-data was updated? The space use can quickly escalate.
SQL Server keeps some stats but something this specific will need to come from a user-defined field.
As a side note, you can make it more difficult to tamper with the date on your created field if you use a lookup table. Create a table "TableName_CreateDate" and use the PK from your actual table and a date value. Your date is in a separate location and less likely to be modified but you can still JOIN on it to get your order. You would need to create a trigger to update this with new values.
If you only want the DATE and don't need a datetime value, you can go one step further and just have a table of dates and a lookup table that joins to that. I.e.:
Table->Table.PK + Date.Pk -> DateTable
This would save a lot of drive space if you have a lot of rows (4 bytes per row I think).
171
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
