A trigger defines a set of actions that are
performed when a specified SQL operation
(such as a delete, insert, or update operation)
occurs on a specified table. When the specified
SQL operation occurs, the trigger is activated
and starts the defined actions.
Benefits of using triggers:
You can use triggers with referential constraints
and check constraints to enforce data integrity
rules. Triggers are more powerful than constraints
because they can also be used to update other
tables, automatically generate or transform
values for inserted or updated rows, and
invoke functions that perform operations
both inside and outside of an IBM® database. For example, instead
of preventing an update to a column if the
new value exceeds a certain amount, a trigger
can substitute a valid value and send a notice
to an administrator about the invalid update.
You can use triggers for defining and enforcing
business rules that involve different states
of the data, such as limiting a salary increase
to 10%. Such a limit requires a comparison
of the value of a salary before and after
an increase. For rules that do not involve
more than one state of the data, consider
using referential and check constraints.
You can use triggers to move the application
logic that is required to enforce business
rules into the database, which can result
in faster application development and easier
maintenance. With the logic in the database,
such as the limit on increases to the salary
column of a table, the IBM database checks the validity
of the changes that an application makes
to the salary column. In addition, the application
programs do not need to be changed when the
logic changes.
You can define triggers by using the CREATE
TRIGGER statement.
The IBM Database Add-Ins for Visual Studio
provide three approaches for creating a trigger in
Visual Studio:
The Trigger view of the IBM Table Designer or the IBM View
Designer, where you can define the trigger
as part of a table definition or view definition, respectively.
The Visual Studio Code Editor, where you can manually
insert the SQL statement in the trigger script file.
To develop an IBM database trigger in Visual Studio: