Introduction
A couple of triggers were throwing the following error which was strange as in other databases the same code works fine. The problematic database is based on an anonymised customer database and was not a copy of a working internal DB.
Error: ORA-25017
Description:
cannot reference NEW ROWID for movable rows in before triggers
Cause:
NEW ROWID was referenced in a before row trigger which is defined on an index-organized table, or a partitioned table with enabled movement of rows. The ROWID cannot be computed in a before row update trigger because it depends on the actual values of the row.
Action:
Remove references to NEW ROWID from the trigger definition.
Actual Resolution:
As we have other databases with the same code working okay, removing the references to NEW ROWID is not the action to fix the issue in this instance.
The key thing to note from the above cause is “partitioned table with enabled movement of rows”. When checking a database with working code it became apparent that there were differences, the following SQL was executed against the anonymised database.
SELECT TABLE_NAME
FROM USER_TABLES
WHERE ROW_MOVEMENT = ‘ENABLED’;
This returned in the region of 26 tables whereas in our internal databases the same query returned just one. The triggers were on tables with row movement enabled which is mentioned as a possible cause.
The suspicion is that the customer had enabled row movement in order to reclaim space used by some large tables and had then not disabled it again.
After disabling row movement on the two tables, the code for the triggers ran fine.
ALTER TABLE <table_name> DISABLE ROW MOVEMENT;
Conclusion:
Although Google doesn’t seem to return any sites with anything other than the standard text from Oracle for this error, don’t jump in with both feet and delete your NEW ROWID references, check for tables with row movement enabled first.