Sql when was a table modified

As expected, the ModifiedDate column is updated but the CreateDate column remains the same. Below is a more detailed explanation of the code used to create the table and its associated trigger.

I created it on the dbo. This includes checking that the Period End time is greater than the Period Start time and that the periods of the different versions of the same row will not overlap. Now the AWBuildVersion table is defined as a temporal table successfully:. The system will hold a schema lock on both the temporal and history table, where these changes will be replicated to the history table. As we discussed previously, the Period start time will be filled by the system with the transaction start time, and the Period End time will be filled by the system with the maximum datetime2 value keeping the records opened.

No changes applied to the history table as no action performed on that rows yet. If you try to query it, you will find it empty. And query the temporal table, we will find that the record is removed from the table, which is the normal situation:. We will find that the deleted record is inserted into the history table, and the Period End time is updated with the transaction start time, marking the row as closed:.

And query the temporal table again, we will find that the row is updated and the Period Start time also updated with the new transaction time, keeping the row opened:. And the old record before the UPDATE operation will be inserted into the history table with the Period End time as the transaction current time, closing the row:. The first clause is the AS OF clause, which returns values after performing the union between the temporal and history tables and filtering the rows that are valid at the specified point in time.

The row can be considered as valid if the Period Start time value is less than or equal to the specified time and that the Period End time is greater than the specified time. Without using triggers, I don't know of a way to keep track of the last time a table was accessed. I know that's been a request for a while now, but I don't think it's possible at the moment. Ashley, I found this article on how to find when a table was last queried or updated while searching for this one.

Maybe it will help you. Very good. Thanks for sharing, Jeff. Its a nice article. But I am more interested in finding what was changed in a table, and what the table looked like prior to the change. You mentioned it could be done using DDL triggers. W3Schools is optimized for learning and training.

Examples might be simplified to improve reading and learning. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content.

While using W3Schools, you agree to have read and accepted our terms of use , cookie and privacy policy.