There are examples of two very simple statement triggers in the
MIMER_STOREschema, both defined against the
PRODUCTStable. They are designed to ensure that the value of the
PRODUCT_SEARCHcolumn is based on the product name. The insert trigger unconditionally performs an update of the
PRODUCT_SEARCHcolumn (setting it to the default value), which will force the update trigger to fire.
The update trigger checks whether the value in the
PRODUCT_SEARCHcolumn is equal to the value returned by the
PRODUCT_SEARCH_CODEfunction when applied to the product name. If the value of
PRODUCT_SEARCHis 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'.
PRODUCT_SEARCH_CODEfunction returns the Soundex value of the product name after it has been processed by the
INDEX_TEXTfunction, which basically involves stripping out any of the words that appear in the
STOP_WORDStable (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_TEXTfunction and the Soundex value returned by the
PRODUCT_SEARCH_CODEfunction would then be `467900'. By using this function against a user input string, it is possible to allow a limited level of fuzzy matching.
Mimer Information Technology AB
Phone: +46 18 780 92 00