How to Understand Triggers in DB2 SQL Context

What Is a Trigger?

Simply stated, a trigger is a piece of code executed in response to an SQL data modification operation; that is, an insert, update, or delete driven by an INSERT, UPDATE, DELETE, or MERGE statement. To be a bit more precise: Triggers are event-driven specialized procedures that are stored in and managed by the DBMS. Each trigger is attached to a single, specified table. A trigger can be thought of as an advanced form of “rule” or “constraint” written using an extended form of SQL.

A trigger cannot be directly called or executed; it is automatically executed (or “fired”) by DB2 as the result of an action—a data modification to the associated table.

After a trigger is created, it is always executed when its “firing” event occurs (INSERT, UPDATE, or DELETE). Therefore, triggers are automatic, implicit, and non-bypassable. Triggers are ignored for certain utilities, but they cannot be bypassed during normal SQL operations.

What is Schema

User-defined functions, user-defined distinct types, stored procedures, and triggers are all associated with a schema. By default, the schema name is the authid of the process that issues the CREATE FUNCTION, CREATE DISTINCT TYPE, CREATE PROCEDURE, or CREATE TRIGGER statement.

1. A schema, therefore, is simply a logical grouping of procedural database objects (user-defined functions, user-defined distinct types, stored procedures, and triggers).
2. You can specify a schema name when you create a user-defined function, type, or trigger. If the schema name is not the same as the SQL authorization ID, the issuer of the statement must have either SYSADM or SYSCTRL authority, or the authid of the issuing process has the CREATEIN privilege on the schema.
3. A DB2 schema is not really a DB2 object. You cannot explicitly CREATE or DROP a schema. The schema is implicitly created when the first DB2 object is created using that schema name.
4. Triggers Are Hybrid DB2 Objects

Triggers are like other database objects, such as tables and indexes, in that they are created using DDL, stored in the database, and documented as entries in the DB2 Catalog.

Triggers also are like stored procedures and check constraints in that they contain code, or logic, and can be used to control data integrity.

Triggers Vs Stored Procedures:

• Triggers are similar in functionality to stored procedures. Both consist of procedural logic that is stored at the database level. However, stored procedures are not event-driven and are not attached to a specific table.
• A stored procedure is explicitly executed by invoking a CALL to the procedure (instead of implicitly being executed like triggers).
• In addition, a stored procedure can access many tables without being specifically associated to any of them; a trigger can modify other tables, but its execution (firing) is tied to a single, specific table. Another significant difference: Stored procedure can return a result set whereas a trigger cannot.

Triggers Vs Check Constraints

• Triggers are similar to table check constraints because triggers can be used to control integrity when data is changed in a DB2 table. However, triggers are much more powerful than simple check constraints because they can be coded to accomplish more types of actions.
• A check constraint is used to specify what data is allowable in a column, but a trigger can do that, plus make changes to data. Furthermore, a trigger can act on data in other tables, whereas a check constraint cannot.
• Additionally, triggers have more knowledge of the database change. A trigger can view both the old value and the new value of a changed column and take action based on that information.
• When deciding whether to use a constraint or a trigger, keep in mind that triggers are more expensive than an equivalent constraint.
• You should always consider the relative cost of executing each. If the task at hand can be completed with either a trigger or a constraint, favor constraints because they are cheaper than triggers and it is usually better to use the cheaper alternative.
• On the other hand, it is wise to consider the long-term costs of both alternatives. Constraints are hard-coded, and a change requires an outage and DBA hours. A trigger can be coded to compare values to a dynamic list, reducing maintenance time and effort.
  Types of DB2 Triggers

4 types of SQL triggers in DB2 context

An INSERT trigger is defined on a table and the logic in the trigger is executed whenever an insert is executed against that table, whether driven by an actual INSERT statement or a MERGE statement that requires insertion.

An UPDATE trigger is defined on a table, and the logic in the trigger is executed whenever an update is executed against that table, whether driven by an actual UPDATE statement or a MERGE statement that requires updating.
And appropriately, a DELETE trigger is defined on a table, and the logic in the trigger is executed whenever a

DELETE statement is issued against that table.

INSTEAD OF trigger, is significantlydifferent than the other three. INSTEAD OF triggers are defined on views and contain the logic required to enable non-updateable views to become updateable. As such, there are INSERT, UPDATE, and DELETE types of INSTEAD OF triggers.