When you insert, update, or delete data in your source table, the text indexes are not updated automatically. After you update data, run sp_refresh_text_index to log the changes to the text_events table. Then, run sp_text_notify to notify the Enhanced Full-Text Search engine that changes need to be processed. The Enhanced Full-Text Search engine then connects to Adaptive Server, reads the entries in the text_events table, determines which indexes, tables, and rows are affected, and updates the appropriate collections.
See sp_refresh_text_index and sp_text_notify for more information on these system procedures.
To have sp_refresh_text_index run automatically after each insert, update, or delete, you can create triggers on your source tables, as follows:
Create a trigger that runs sp_refresh_text_index after a delete operation.
Create a trigger that runs sp_refresh_text_index after an insert operation.
Create a trigger that runs sp_refresh_text_index after an update operation to an indexed column.
Triggers are not fired when you use writetext to update a text column. To have sp_refresh_text_index automatically run after a writetext:
Set up a non-text column and update that column after each writetext.
Create a trigger on the non-text column to run sp_refresh_text_index. Since the Enhanced Full-Text Search engine reinserts the entire row when you issue sp_text_notify, the update to the text column gets propagated to the text index.
For examples of each of these triggers, see the sample script sample_text_main.sql in the $SYBASE/$SYBASE_FTS/sample/scripts directory.
