We have 2 servers.
Production server with Payment DB (P01), which is replicated via certain tables to Data-warehouse server (DW01)
The wish is to create triggers on one table that has 800-1000 per second transactions to capture the following
I have 2 questions:
1. Would the trigger work better from PB01 via linked server to DW01
2. Or better to create the trigger on the replicated table on DW01.
3. With so many records possibly being inserted - the business only wants to retain 30 days of rolling information - im wondering what sort of purge would be best for this sort of table that
Data audited should include (but not be limited to):
Date of change
From Status Id
From Sub Status Id
To Status Id
To Sub Status Id
The format of the data should be such as to allow easy lookup and handling