A trigger is a piece of code that is automatically executed, or fired in response to a data modification event on a table. The data modification event include insert, update, delete driven by an INSERT, DELETE, UPDATE, and MERGE statement. Db2 stores the triggers in the database catalog, where each trigger is attached to a single table. You cannot call a trigger directly, only Db2 can call the trigger when an event occurs on the table.
The CREATE TRIGGER statement allows you to create a new trigger or replace an existing trigger in the database. The basic syntax for the CREATE TRIGGER statement is the following -
CREATE [OR REPLACE] TRIGGER trigger_name
[NO CASCADE] {AFTER | BEFORE | INSTEAD OF} trigger_event
ON { table_name |view_name }
REFERENCING {OLD AS | NEW AS | OLD TABLE AS | NEW TABLE AS}
{correlation_name |identifier}
{FOR EACH ROW | FOR EACH STATEMENT}
action;
Syntax
First, specify the name of the trigger after the CREATE TRIGGER keywords. If you use CREATE OR REPLACE TRIGGER, Db2 will create the trigger if it does not exist or replace in case the trigger already exists.
Second, specify the event that activates the trigger e.g., INSERT, UPDATE, or DELETE.
Fourth, specify a correlation name that identifies the row state before triggering the operation.
Fifth, specify a temporary table name that identifies the set of affected rows before triggering the operation.
Sixth, specify the action to perform when a trigger is fired.
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.
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.
A schema, therefore, is simply a logical grouping of procedural database objects (user-defined functions, user-defined distinct types, stored procedures, and triggers).
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.
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.
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.
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.
Triggers are a powerful feature of DB2 for z/OS and OS/390. They enable non-bypassable, event-driven logic to be intrinsically intermingled with data. The following guidelines can be used to help you implement effective and efficient triggers for your DB2 databases and applications.
Naming Triggers
A trigger name, along with its schema, must be unique within the DB2 subsystem. The schema name that qualifies the trigger is the owner of the trigger. The schema name for the trigger cannot begin with the letters 'SYS' , unless the schema name is 'SYSADM' Because the trigger name is also used for the trigger package name, the trigger name cannot be the name of a package that already exists. For trigger packages, the schema of the trigger is used as the collection of the trigger package. The combination of schema.trigger must not be the same as an independently existing collection.package combination.
Keep It Simple
Each trigger should be coded to perform one and only one task. The trigger should be as simple as possible while still performing the desired task. Do not create overly complex triggers that perform multiple, complex tasks . It is far better to have multiple triggers, each performing one simple task, than to have a single, very complex trigger that performs multiple tasks. A simple trigger will be easier to code, debug, understand, and maintain when it needs to be modified.
Implement Triggers with Care
After a trigger is created, it impacts change processing for every user and program that modifies data in the table on which the trigger is defined. Because of this global nature of triggers, take great care to implement only thoroughly tested and debugged triggers.
Trigger Limitations
There are limits on how triggers can be used. For example, you cannot define triggers on -
A DB2 system catalog table A view An alias A synonym Any table with a three-part name
You can create triggers on your PLAN_TABLE , DSN_STATEMNT_TABLE , or DSN_FUNCTION_TABLE . But you should not define INSERT triggers on these tables because the triggers will not be fired when DB2 adds rows to the tables.
BEFORE Versus AFTER Triggers
Assign the trigger activation specification carefully . Remember that a BEFORE trigger cannot cascade and fire other triggers because it cannot UPDATE data.
FOR EACH ROW Versus FOR EACH STATEMENT
Understand the implication of the granularity of the trigger. A statement-level trigger, one specifying FOR EACH STATEMENT , will only fire once. If you need to examine the contents of impacted columns , you will need a row-level trigger, one specifying FOR EACH ROW .
Also, remember that you cannot specify FOR EACH STATEMENT for a BEFORE trigger.
Using Triggers to Implement Referential Integrity