How to save modifier identifier in system versions of temporary tables?

Question

I have a table with the version of the system on sql 2016 server. I want to save the modifier identifier in my temporary table (history), while users are deleting or updating on the table.

Is there a built-in sql server 2016 solution for this?

Answer 1

You can use Audit

Introduction to SQL Server 2008 Auditing

Answer 2

No, this is not possible, because Temporal tables and their History tables must have exactly the same schema i.e.

• number of columns
• column names
• data types
• and even column ordering

Thus, if you do not register the modifier identifier in the Temporal tables themselves, you cannot add this information to the history table.

How would you capture this information about the modifier identifier in the Temporal table itself, so that this can be processed at the application level.