This is a possible workaround. But even if a table has a primary key, it seems like there's some inefficiency in doing things this way: the old and new row versions start out linked together (for instance this information is available in a FOR EACH ROW trigger), but we're throwing away that information by splitting them into two separate relations, forcing us to have to join them back up again. Wouldn't it make sense to expose a transition relation where the correspondence between old and new versions is never discarded in the first place? Or is there something I'm missing?
Also, there are cases where it may not be desired to have a primary key, as the index maintenance and constraint checking are not free and not always necessary. And if one wants to implement a general change data capture setup, it would nice to be able accomodate such tables without having to alter them.
I'd be happy to try to work out an implementation of REFERENCING CHANGE TABLE if there's support for the idea. Or is there some problem with this, or some better way of achieving the goal?
- Brent
