I have a requirement to mirror insert/update/delete operations from one table to another. For example insert on tableA has to be copied into tableB, update to tableA applied to tableB, and delete from tableA be applied to tableB. It's as simple as that, except tableB has 1 additional column for a constant value, so very simple triggers are needed.
I'm not sure if it is better to write 3 separate triggers, or have one trigger that does all of the operations.
This is for 3 databases: Sybase ASE, MSSQL and Oracle, and I'd like to keep it the solution similar (so either 3 for all databases or 1 for all of them).
Is it just a matter of preference, to have 3 triggers vs. 1, or are there actual benefits to either solution?
Assuming that you actually need a trigger and that table B cannot simply be defined as a view on top of table A or that table B cannot just be defined with a foreign key that references a row in A along with the constant, that A cannot be redefined to add the additional column (potentially with a default value of the constant), one trigger at least lets you keep all the related logic in one place rather than having multiple places that need to be updated when you do something like add a new column to A. But I would be extremely wary of any architecture that involved having two different tables reflecting essentially the same data in both. That violates normalization, it adds to the system's I/O workload, and it makes the whole system more complex.