DB2 9.5 SQL Procedure Developer certification Exam 735 preparation, Part 4: Triggers

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.