What's the best way to update a closure table?

Question

I have a table relating records using the adjacency list method (table A), and another table relating the same records using a closure table (table B). They both capture the same graph, so they both need to be kept in sync.

The question is, what's the best way to update the closure table?

As I see it, there are three alternatives:

1. Triggers. On INSERT/UPDATE/DELETE at A, run a sproc that calculates the new closures. Cons: changes to A result in a long, synchronous (locking?) operation; possible deadlocks (?).
2. Application code. Narrow down changes in A to Add/Update/Delete methods (eg. a repository pattern), and overload them with calls to a sproc that calculates the new closures. Cons: extra round trip to the DB; possible integrity issue if another thead modifies A or B in a contrary way at the same time; possible integrity issue if, say, another application decides to modify A and not B.
3. Background updater. Write a secondary process that continually looks for updates to A and makes the corresponding updates to the closure table. Cons: complex (extra service to write and manage); windows without synchronization.

Even if there is no "best" option, any thoughts on the trade-offs would be most appreciated!

Answer 1

If your hierarchies are anything like as mostly static as most I've dealt with, I would probably go with the trigger. Really depends on the update frequency and the read load.