YOUR IBM i TRAINING SOURCE

You must allow the browser to execute JavaScript programs if you want to view product information. If your browser is set up to execute JavaScript programs, the problem is that the product ID was incorrect. Use the menu bar above to find the desired product.

Overview

All organizations have business rules that affect the creation and storage of data. This course details how such rules can be built into a database so that they are enforced by the Db2 database manager rather than by individual application programs. Two mechanisms by which business rules can be enforced by Db2 are called constraints and triggers.

The course begins by describing the how constraints limit the data that can be stored in a Db2 database. Primary key, unique, and check constraints are discussed first, followed by referential constraints. With all four types of constraints, you will see how to define a constraint when you create a table, how to add a constraint later, how to alter a constraint, and how to remove a constraint when it is no longer needed. You will also see how an application program that uses a table can determine when a constraint violation has occurred.

The effect of a constraint is to limit what can happen to a table during an insert, an update, or a delete operation. In contrast, a trigger is a set of one or more SQL statements that is executed automatically when a specified database event occurs. You will see common applications for triggers and learn the options available for defining them. The course then covers the SQL statements for creating, enabling or disabling, and dropping triggers.

Approximate Study Time: 3 hours

Objectives

After completing this course, you should be able to:

Define and use relational database terminology related to constraint and triggers
Create a table with a compound primary key
Create a table with a unique, check, or referential constraint
Name and define the various referential constraint rules
Display the constraint and trigger information for a table
Describe the issues involved when adding a constraint to an existing table
Alter a table to add a unique, check, or referential constraint
Alter a table to remove a constraint
Identify the various referential constraint states
Describe how an RPG program, a COBOL program, or an SQL stored procedure can determine whether a constraint violation has occurred
Describe the function and capabilities of a trigger
Identify the database events that can cause a trigger to fire
Identify appropriate applications for a trigger
Identify the different types of triggers that can be assigned to a table
Define what is meant by cascading triggers and recursive trigger firing
Identify situations in which a multiple-purpose trigger is appropriate
Code the SQL statements to:
- Create a trigger
- Enable or disable a trigger
- Remove a trigger

Topic Outline

Using Constraints

Referential Integrity

Implementing Constraints

Trigger Concepts

Implementing Triggers

Audience

This course is intended for programmers and application designers who will be either creating SQL constraints and triggers or working with applications involving tables which use them.

Prerequisites

This course assumes that you have a working knowledge of basic IBM i concepts and facilities. You can satisfy this prerequisite by successfully completing the courses in the following series:

Introduction to the IBM i Environment

The course also assume you familiar with IBM i programming concepts and facilities. You can satisfy this prerequisite by successfully completing the courses in the following series:

The IBM i Programming Environment

Finally, the course assumes that you have a basic knowledge of SQL and can code SQL statements and procedures. You can satisfy this prerequisite by successfully completing the following courses in this series:

SQL Fundamentals
SQL Stored Procedures

In addition, if you will be taking any of the following courses on embedded SQL, we recommend that you do so before beginning this one.

Coding SQL Statements in a Free-Form RPG Program
Coding SQL Statements in an RPG Program
Coding SQL Statements in a COBOL Program