Triggers writing to audit table on another server.

Question

question

mooriginal asked Nov 14, '22

Triggers writing to audit table on another server.

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):
1. Request IDs
2. Date of change
3. Data changed
  1. From Status Id
  2. From Sub Status Id
  3. To Status Id
  4. To Sub Status Id
The format of the data should be such as to allow easy lookup and handling

sql2012 database-triggers

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2022-11-16T15:32:49Z

anthony.green answered Nov 16, '22

Put the trigger on the subscriber (DW) side of the topology. Anything you do on the Payment side will only slow down transactional performance.

As for purging put the needed indexes on the date columns then you can easily delete data based on your aging criteria.

As for the formatting that is up to you to develop to meet the business requirements.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question