Triggers

---

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.

Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se