Before you start
About this series
This six-part series on DB2 SQL Procedure Developer describes all the basic constructs and methods of SQL procedural Language, and explains how to use it in stored procedures, user-defined functions, and triggers, including error handling and deployment. Also describes some of the DB2 9.5 advanced features, such as optimistic locking, layered queries, and declared global temporary tables. This series will provide details on how to call stored procedures, user-defined functions and triggers, and how to share data between procedures and functions. It will introduce DB2 development tools, including IBM Data Studio. These tutorials will provide a solid foundation for all parts of the exam. However, you should not rely solely on these tutorials to prepare for the certification exams.
About this tutorial
This tutorial describes concepts for identifying trigger usages, trigger types, creating triggers and their required permissions, and related trigger actions.
Goal
After completing this tutorial, you should:
Ability to recognize the correct use of triggers
Understanding Trigger Types
Understanding the permissions required to create triggers
Learn how to create triggers
Ability to create, deploy, and identify trigger actions
Familiarize yourself with the advanced use of triggers
Prerequisite conditions
To participate in the DB2 9.5 SQL Procedure Developer Exam, you must have passed DB2 9 Family Certification examination (Exam 730). You can use the DB2 9 Fundamentals certification Exam 730 Preparation series to prepare for the test. It is a very popular series that has helped many people understand the fundamentals of the DB2 series.
This tutorial is written for elementary and intermediate DB2 programmers. You should have a general background on how relational databases work, and have a basic understanding of database, database programming constructs, and operating system security. You should also be proficient with the DB2 Command line Processor (CLP) and should have knowledge of SQL work.
System Requirements
To run the examples in this tutorial, you need access to the sample database that is included with the DB2 9.5 database server and DB2 (command line Processor executes DB2SAMPL commands to create a sample database).
Introduction to Triggers
Triggers are optional actions that are performed when a database insert, update, or delete operation is performed on a particular table. Triggers are created using the Create TRIGGER DDL statement.
How to identify the correct usage of triggers
Data processing operations, such as inserts, updates, or deletes, can raise the startup event of a trigger. Check limits and reference restrictions are areas where triggers can be used. Triggers can be used to:
Update other tables and views
Automatically generate values
Record value Conversion
Generate alerts
Defining and enhancing business rules
Enhancing data integrity rules
Trigger type
Before triggers: These triggers are executed before the database inserts or updates are applied to the table. Call and signal are two SQL statements that are allowed to be used.
Before delete trigger: Executes before the delete operation is applied.
After trigger: Executes after applying an update, insert, or delete operation. These triggers are used to update data in other tables that reflect relationships and consistency between tables, and to ensure data integrity. After triggers are typically used to generate alerts to the user under certain circumstances.
INSTEAD of triggers: These triggers support the operation of views that initially do not support INSERT, UPDATE, and delete operations.