There are examples of two very simple statement triggers in the MIMER_STORE schema, both defined against the PRODUCTS table. They are designed to ensure that the value of the PRODUCT_SEARCH column is based on the product name. The insert trigger unconditionally performs an update of the PRODUCT_SEARCH column (setting it to the default value), which will force the update trigger to fire.
The update trigger checks whether the value in the PRODUCT_SEARCH column is equal to the value returned by the PRODUCT_SEARCH_CODE function when applied to the product name. If the value of PRODUCT_SEARCH is inconsistent with the product name then an update is performed; this will cause a recursive call of the update trigger. Note that an update statement causes the trigger to fire even when no rows are updated, hence the use of 'if exists'.
The PRODUCT_SEARCH_CODE function returns the Soundex value of the product name after it has been processed by the INDEX_TEXT function, which basically involves stripping out any of the words that appear in the STOP_WORDS table (i.e. the 100 most common words in the English language). As an example, the album title 'Same As It Ever Was' is reduced to 'Same Ever' by the INDEX_TEXT function and the Soundex value returned by the PRODUCT_SEARCH_CODE function would then be '467900'. By using this function against a user input string, it is possible to allow a limited level of fuzzy matching.
Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40