history table in sql server if any insert update delete happened on tables

Question

User-1634604574 posted

I have 2 tables, Table-A and Table-A-History.

• Table-A contains current data rows.
• Table-A-History contains historical data

I would like to have the most current row of my data in Table-A, and Table-A-History containing historical rows.

I can think of 2 ways to accomplish this:

1. whenever a new data row is available, move the current row from Table-A to Table-A-History and update the Table-A row with the latest data (via insert into select or select into table)

i want to use one trigger for all tables in db if any table inserted new row save that new row in Table-A-History

any delete happened also save it in Table-A-History

and any record updated  which column updated save it in Table-A-History

Answer 1

User753101303 posted

Hi,

Which SQL Server version do you have? Starting with SQL Server 2016 you have https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver15 (and it seems available as well in the Express edition).

If using an earlier version you could do something close (note in particular that you have columns to tell during which time frame the row was current).

Also it doesn't seems you really ask a question. If you want to help to get a single trigger for all tables it would be harder as you'll need to generate the SQL statement before running it (another option could be to generate the trigger code).

Answer 2

User-1634604574 posted

except trigger can i do it by procedure or function?

can you write sample?