CTXCAT index not updating after UPDATE on calculated column

275901

I am working with Oracle 9.2. I have a CTXCAT index on a column named "LAST_NAME_FIRST." This column is calculated in a BEFORE INSERT OR UPDATE trigger.

I found that the following statement

UPDATE <table> SET LAST_NAME='XXX';

results in the LAST_NAME_FIRST column being updated, but the AFTER trigger for synchronizing the CTXCAT index on LAST_NAME_FIRST is not firing (since my update statement does not include the indexed column). If I issue the following statement

UPDATE <table> SET LAST_NAME='XXX', LAST_NAME_FIRST=<any value>;

then the LAST_NAME_FIRST column gets calculated appropriately, and the AFTER trigger for the CTXCAT index fires, synchronizing the index.

Is this the only workaround available? Oracle, would it not be better to generate the CTXCAT trigger to examine the :old and :new values in the indexed column, rather than using a check on (if updating(LAST_NAME_FIRST))?

Thanks,

Jim Tilson

Barbara Boehmer

In Oracle 9i, you must update the indexed column for it to be synchronized. If you upgrade to Oracle 10g, you can just use sync(on commit) in your parameters when creating the index.

Barbara Boehmer

Please disregard the previous response. Although sync(on commit) will take the place of ctx_ddl.sync_index, you still have to update the indexed column, for synchronization to occur.

17531

I fixed this issue by adding additional UPDATING (first_name and last_name) conditions in the DR$table_name trigger.

Here is part of the modified trigger.

if (inserting or updating('LAST_NAME_FIRST') or updating('FIRST_NAME') or updating('LAST_NAME')) then reindex := TRUE;