Create triggered actions
An SQL trigger is a database mechanism that executes an action automatically when a certain event occurs. The event that can trigger an action can be an INSERT, DELETE, or UPDATE statement on a specific table. The table on which the triggered event operates is called the triggering table.
An SQL trigger is available to any user who has permission to use it. When the trigger event occurs, the database server executes the trigger action. The actions can be any combination of one or more INSERT, DELETE, UPDATE, EXECUTE PROCEDURE, or EXECUTE FUNCTION statements.
Because a trigger resides in the database and anyone who has the required privilege can use it, a trigger lets you write a set of SQL statements that multiple applications can use. It lets you avoid redundant code when multiple programs need to perform the same database operation. By invoking triggers from the database, a DBA can ensure that data is treated consistently across application tools and programs.
When you create a trigger, you can define an INSTEAD OF trigger on a view. An INSTEAD OF trigger replaces the INSERT, DELETE, or UPDATE trigger event with a specified trigger action on a view.
- Create an audit trail of activity in the database
For example, you can track updates to the orders table by updating corroborating information in an audit table.
- Implement a business rule
For example, you can determine when an order exceeds a customer's credit limit and display a message to that effect.
- Derive additional data that is not available within a table or
within the database
For example, when an update occurs to the quantity column of the items table, you can calculate the corresponding adjustment to the total_price column.
For more information about triggers, See the Informix® Guide to SQL: Tutorial and the Informix Guide to SQL: Syntax.