How to Keep Modifier ID in System Versioned Temporal Tables?

Question

I have a system-versioned table in sql server 2016. I want to store Modifier ID in its temporal (history) table while users are performing delete or update action on the table.

Is there any built-in solution by sql server 2016 to do that?

Zygophyte

Answer 1

You can use Audit

Introduction to sql server 2008 audit

Schwerin

Answer 2

No it is not possible because the Temporal tables and their History tables has to have exactly the Same Schema i.e

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

So unless you are capturing the Modifier's ID in the Temporal tables itself, you cannot add this information to the history table.

How would you capture this Modifier's ID information in the Temporal table itself, well this can be handled on the application layer.

Hypocotyl

That's a drawback for me. They can at least add an option to mark such column as hidden from the original table (like the period columns), – Treiber