SQL Trigger for maintaining modification history

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

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.

You should have different historic tables for each normal table you have. Then you can place a trigger in each normal table.

Finally, if you need, you can create a view CustomerHistory joining the different historic tables.

You need to add your trigger to the table where data changes will "trigger" a need to do something.

So a trigger on the Customers table to track CustomerAddress changes and a trigger on CustomerAccountManagers when the ManagerId is changed.