SQL Trigger for maintaining modification history

Question

I have three tables:

Table1: Customers (CustomerId, Name, CustomerAddress)
Table2: AccountManagers(ManagerId, Name)
Table3: CustomerAccountManagers (CustomerId, ManagerId, Remarks)
Table4: CustomerHistory(CustomerAddress, ManagerId, Date)

The CustomerHistory table is used to store any changes made to "CustomerAddress" OR "ManagerId" for example CustomerAddress is updated from "Address1" to "Address2" OR The CustomerAccountManager changes from "Manager1" to "Manager2".

I need to store the changes in CustomerHistory table through SQL Trigger. The issue is that the on which table should i have my trigger? Please note that the changes are made at the same time to both the tables "Customers" & "CustomerAccountManagers".

Thanks

Answer 1

First of all, the CustomerHistory table should probably contain CustomerId as well, so that a history record can be tracked back to the proper customer.

You'll need two triggers: one on CustomerAccountManagers and one on Customers. If you can guarantee the order in which they are executed it's fine: the first trigger will insert, the second update the history record.

If you cannot guarantee the order, things get complicated, as each trigger would have to: 1) try to insert a new record, and failing that 2) update the existing. You'd have to protect yourself from the intermittent insertion by another trigger and this likely means running within a transaction with serializable isolation level (locking the whole table). This is deadlock-prone, so it's really better to use two history tables, as others already suggested.