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
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
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.
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