Table with an update trigger defined is out of sync after adding a new column with a default value (4332656)

Return

Feedback Submitted

Did this article solve an issue for you?

Select Rating

Title

Table with an update trigger defined is out of sync after adding a new column with a default value
Description

After adding a new column with default value to a table with an update trigger defined, the table goes out of sync (OOS). The trigger fires to update other existing column with sysdate whenever there is any update to the table.
Cause

With Oracle 11g, whenever adding a new column with default value, Oracle will keep the information as meta-data in dictionary instead of updating all the rows physically, but from Oracle side, it still consider it as an ““update” to the new column, therefore, when there is an update trigger for the table, the trigger will fires , which updates other column of the table in this case. The DMLs generated by the trigger are recursive DML that're generated by "alter table add column <default>". SharePlex doesn't replicate recursive DML.
Resolution

Workarounds:
1. Disable the trigger prior to “alter add table add column default <>”
2. Revise the trigger to only fire when DMLs are done by application users
3. If the data that updated by trigger are not necessary to replicate to the target, then set up sharePlex vertical partition to exclude this column from the replication

Feedback Submitted

Did this article solve an issue for you?

Select Rating

Title