Forum Stats

  • 3,828,128 Users
  • 2,260,865 Discussions
  • 7,897,487 Comments

Discussions

CTXCAT index not updating after UPDATE on calculated column

275901
275901 Member Posts: 1
edited Mar 12, 2008 11:50AM in Text
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

Comments

This discussion has been closed.