DB2 9.5 SQL Procedure Developer exam 735 prep, Part 4: Triggers

Gain an understanding of the fundamental concepts behind IBM® DB2® triggers — when, how, and what kind of triggers can be used under various circumstances and the required user privileges. This tutorial is Part 4 of a series of tutorials designed to help you prepare for the IBM Certified Solution Developer - DB2 9.5 SQL Procedure Developer exam (Exam 735).

Hareendranath Kattana, Enablement Consultant, IBM

Hareendranath KattanaHareendranath Kattana has more than 17 years of IT experience, and has been an IBM data management enablement consultant and a technical alliance manager supporting strategic business partners for the past eight years. He is a certified project management professional from the PMI organization, and has successfully executed several large integration and database migration projects with our partners.



James Merry (jmerry@us.ibm.com), Enablement Consultant, IBM

James MerryJames Merry is an enablement consultant working with IBM Business Partners to help them enable their applications to DB2. He most recently was a member of the IBM Content Management eMail Compliance team. He has been working in the relational database industry for over 20 years developing database applications and database administration.



26 February 2009

Before you start

About this series

This series of six DB2 SQL Procedure Developer tutorials covers all the basic constructs and methods of the SQL Procedural Language and shows how it can be used in stored procedures, user-defined functions, and triggers, including error handling and deployment. It also covers some DB2 9.5 advanced features like optimistic locking, hierarchical queries, and declared global temporary tables. This series provides specifics on how stored procedures, user-defined functions, and triggers can be invoked, and how you can share data between procedures and functions. It introduces DB2 development tools, including IBM Data Studio. These tutorials provide a solid base for each section of the exam. However, you should not rely on these tutorials as your only preparation for the exam. More exam resources can be found in the Resources section of this tutorial.

About this tutorial

In this tutorial, get an introduction to the concepts of identifying trigger usage, trigger types, creating a trigger and its required privileges, and associated trigger actions.

Objectives

After completing this tutorial, you should :

  • Be able to identify proper usage of triggers
  • Understand trigger types
  • Understand the required privileges for creating a trigger
  • Know how to create a trigger
  • Be able to create, deploy and identify trigger actions
  • Be familiar with advanced uses for triggers

Prerequisites

To take the DB2 9.5 SQL Procedure Developer exam, you must have already passed the DB2 9 Family Fundamentals exam (Exam 730). You can use the DB2 9 Fundamentals certification 730 prep series to prepare for that exam. It is a very popular series that has helped many people understand the fundamentals of the DB2 family of products.

This tutorial is written for DB2 programmers whose skills and experience are at an introductory to intermediate level. You should have a general background knowledge of how a relational database works as well as basic knowledge of databases, database programming constructs, and operating system security. You should also be familiar with using the DB2 Command Line Processor (CLP), and you should have a working knowledge of SQL.

System requirements

To run the examples in this tutorial, you need access to a DB2 9.5 database server and the SAMPLE database that is provided with DB2. (The SAMPLE database can be created by executing the command db2sampl from the DB2 Command Line Processor.)


Introduction to triggers

Triggers are optional actions that are executed in response to a database insert, update, or delete operation on a particular table. Triggers are created using the CREATE TRIGGER DDL statement.

How to identify proper usage of triggers

Data manipulation operations, such as insert, update, or delete, can initiate the launching of a trigger event. Check constraints and referential constraints are areas where triggers can be used. Triggers can be used to:

  • Update other tables and views
  • Automatically generate values
  • Record value transformation
  • Generate alerts
  • Define and enforce business rules
  • Enforce data Integrity rules

Trigger types

  • BEFORE triggers: These are executed before a database insert or update is applied against a table. CALL and SIGNAL are two SQL statements allowed.
  • BEFORE DELETE triggers: Executed before a delete operation.
  • AFTER triggers:Executed after an update, insert, or delete operation. These are used to update data in other tables that would then reflect the relationship and consistency between the tables, and to also ensure data integrity. AFTER triggers are often used to generate alerts to users under specific circumstances.
  • INSTEAD OF triggers: These enable the execution of insert, update and delete against views that are complicated to natively support these operations. Please refer to the section "Advanced usage of Triggers" to learn more.

Required privileges for creating a trigger

The schema ID that is working on a trigger must have one of the following privileges:

  • ALTER privilege on the table on which the BEFORE or AFTER trigger is defined
  • CONTROL privilege on the view on which the INSTEAD OF TRIGGER is defined
  • Owner of the view on which the INSTEAD OF trigger is defined
  • ALTERIN privilege on the schema of the table or view on which the trigger is defined
  • SYSADM or DBADM authority

and any one of:

  • IMPLICIT_SCHEMA database authority, if implicit or explicit schema name of the trigger does not exist
  • CREATEIN privilege on the schema, if the schema name of the trigger refers to an existing schema

Assuming the authorization ID does not have SYSADM and DBADM authority and the trigger exists, this ID must have all the following privileges:

  • SELECT privilege on the table on which the trigger is defined — for transition variables, tables, or both
  • SELECT privilege on any table or view referenced in the triggered action condition
  • Necessary privileges to invoke the triggered SQL statements specified

Note: An ID with SYSADM authority that only creates a trigger is also explicitly granted DBADM authority.

Create a trigger statement

Triggers can be created dynamically with SQL or embedded within an application, provided the creator has the required set of rights. Please refer to the DB2 9.5 Linux, UNIX, and Windows Information Center, in the section "Create Trigger" statement for details on CREATE TRIGGER authorizations (see Resources). Listing 1 shows you the basic syntax used to create a trigger:

Listing 1. General syntax diagram for CREATE TRIGGER statement
	                          .-NO CASCADE-.
>>-CREATE TRIGGER--trigger-name--+-+------------+--BEFORE-+----->
                                 +-AFTER------------------+
                                 '-INSTEAD OF-------------'

>--+-INSERT--------------------------+--ON--+-table-name-+------>
   +-DELETE--------------------------+      '-view-name--'
   '-UPDATE--+---------------------+-'
             |     .-,-----------. |
             |     V             | |
             '-OF----column-name-+-'

>--+------------------------------------------------------------------+-->
   |              .-------------------------------------------------. |
   |              V  (1)    (2)          .-AS-.                     | |
   '-REFERENCING------------------+-OLD--+----+--correlation-name-+-+-'
                                  |      .-AS-.                   |
                                  +-NEW--+----+--correlation-name-+
                                  |            .-AS-.             |
                                  +-OLD TABLE--+----+--identifier-+
                                  |            .-AS-.             |
                                  '-NEW TABLE--+----+--identifier-'

>--+-FOR EACH ROW--------------+--| triggered-action |--------->
   |  (3)                      |
   '--------FOR EACH STATEMENT-'

triggered-action

|--+-------------------------------------+---------------------->
   |  (4)                                |
   '--------WHEN--(--search-condition--)-'

In order to understand the syntax diagram in Listing 1, let's create several simple triggers that demonstrate the basic syntax of the CREATE TRIGGER statement. A trigger has three main components:

  • Trigger event
  • Trigger condition
  • Trigger action

Find an explanation of each of these components with the following examples.

Listing 2 shows the syntax for an AFTER INSERT trigger. INSERT is one of the trigger events. This trigger is activated (fired) when rows are inserted into the base table employee. Because this is defined as an AFTER trigger, the timing condition is performed after an INSERT statement on employee table. The trigger action is to insert an audit entry into the audit_emp base table. REFERENCING NEW AS provides a correlation name that specifies the prefix you assign to allow for referencing the new values of columns affected by the trigger. FOR EACH ROW is another timing condition. The trigger actions are executed for each row the trigger event processes, hence, if the INSERT statement processes 25 rows, the audit_emp table is inserted with 25 new audit entries. Listing 2 shows the syntax for this trigger.

Listing 2. A simple AFTER INSERT trigger
CREATE TRIGGER new_emp
  AFTER INSERT ON employee
  REFERENCING NEW AS n
  FOR EACH ROW
  INSERT INTO audit_emp VALUES (n.empno, 'Insert',0.0, current user, current timestamp)

Listing 3 shows the syntax for the AFTER DELETE trigger. Delete is one of the trigger events. This trigger is activated (fired) when rows are deleted from the base table employee. Because this is defined as an AFTER trigger, the timing condition is performed after a DELETE statement on employee table. The trigger action is to insert an audit entry into the audit_emp base table. REFERENCING OLD AS provides a correlation name that specifies the prefix you assign to allow for referencing the old values of columns affected by the trigger. FOR EACH ROW is another timing condition. The trigger actions are executed for each row the trigger event processes, hence, if the DELETE statement processes five rows, the audit_emp table is inserted with five new audit entries.

Listing 3. A simple AFTER DELETE trigger
CREATE TRIGGER purge_emp
  AFTER DELETE ON employee
  REFERENCING OLD AS o
  FOR EACH ROW
  INSERT INTO audit_emp VALUES (o.empno, 'Delete',0.0, current user, current timestamp)

Listing 4 shows the syntax for the AFTER UPDATE trigger. Update is one of the trigger events. This trigger is activated (fired) when updates are performed on the base table employee. Because this is defined as an AFTER trigger, the timing condition is performed after an UPDATE statement on employee table. The trigger action is to insert an audit entry into the audit_emp base table. REFERENCING OLD AS provides a correlation name that specifies the prefix you assign to allow for referencing the old values of columns affected by the trigger, as does the REFERENCING NEW clause. FOR EACH ROW is another timing condition. The trigger actions are executed for each row the trigger event processes, hence, if the UPDATE statement processes 100 rows, the audit_emp table is inserted with 100 new audit entries. The WHEN clause in the trigger body provides for conditional logic to be included within the FOR EACH ROW. This provides for the trigger action to be conditional on the results of a test you define using the correlation names declared in the REFERENCE clause. This example compare the existing employee.salary column in the base table employee to a newly updated value from the UPDATE statement. If the condition is true (not equal), you then perform the INSERT statement on the audit_emp table using the newly updated value from the UPDATE statement on the employee table.

Listing 4. A simple AFTER UPDATE trigger
CREATE TRIGGER update_emp
  AFTER UPDATE OF salary ON employee
  REFERENCING OLD AS o NEW AS n
  FOR EACH ROW
  WHEN (n.salary <> o.salry)
  INSERT INTO audit_emp VALUES (o.empno,'Update',n.salary,current user, current timestamp)

Listing 5 shows the syntax for the BEFORE UPDATE trigger. Again, update is one of the trigger events. This trigger is activated (fired) when updates are performed on the base table employee. Because this is defined as a BEFORE trigger, the timing condition is performed before an UPDATE statement on the employee table. The trigger action sets the employee.salary to 75,000.00 if the newly updated value for salary table is equal to 60,000.00. REFERENCING OLD AS provides a correlation name that specifies the prefix you assign to allow for referencing the old values of columns affected by the trigger, as does the REFERENCING NEW clause. FOR EACH ROW is another timing condition. The WHEN clause in the trigger body provides for conditional logic to be included within the FOR EACH ROW. This provides for the trigger action to be conditional based on the results of a test you define using the correlation names declared in the REFERENCE clause. This example compares the updated value in the UPDATE statement (employee.salary). If it is equal to 60,000.00, you set the salary to 75,000.00.

Listing 5. A simple BEFORE UPDATE trigger
CREATE TRIGGER update_bemp
  BEFORE UPDATE ON employee
  REFERENCING OLD AS o NEW AS n
  FOR EACH ROW
  WHEN (n.salary = 60000.00)
  SET n.salary = 75000.00)

Advanced usage of triggers

Some of the advanced ways in which you can use triggers include the following:

  • INSTEAD OF triggers
  • Processing XML data with triggers
  • Invoking a stored procedure from within a trigger

INSTEAD OF triggers

INSTEAD OF triggers are always created for views, never for base tables or nicknames. By using INSTEAD OF triggers, view modifications are replaced by trigger logic that functions on behalf of a previously defined view and are transparent to the calling application.

Listing 6 shows how to create an INSTEAD OF trigger:

Listing 6. Create INSTEAD OF trigger
-- Base Table (employees)
CREATE TABLE "DB2INST1"."EMPLOYEES"
   (
      "EMPNO"    CHAR(6) NOT NULL     ,
      "FIRSTNME" VARCHAR(12) NOT NULL ,
      "LASTNAME" VARCHAR(15) NOT NULL ,
      "PHONENO"  CHAR(4)              ,
      "SALARY"   DECIMAL(9,2)
   )


-- Create View for the EMPLOYEES Table
CREATE VIEW employeev AS
SELECT empno, firstnme, lastname, phoneno
FROM employees


-- Create Instead of Trigger
CREATE TRIGGER new_emp1
  INSTEAD OF INSERT ON employeev
  REFERENCING NEW AS n
  FOR EACH ROW
  INSERT INTO employees VALUES (n.empno, n.firstnme, n.lastname, n.phoneno, 0)

An INSTEAD OF trigger allows for inserting data into the protected column salary in the employees table. This trigger is activated (fired) when rows are inserted into the view employeev. Because this is defined as an INSTEAD OF trigger, the timing condition is performed after an INSERT statement on the view. The trigger action performs an INSERT in the base table (employees). REFERENCING NEW AS provides a correlation name that specifies the prefix you assign to allow for referencing the new values of columns affected by the trigger. FOR EACH ROW is another timing condition. The trigger actions are executed for each row the trigger event processes, hence, if the INSERT statement processes five rows, the base table (employees) is inserted with five new entries and provides a default value of zero for the salary column not visible from the employeev view.

Processing XML data with triggers

CREATE TRIGGER can be used to create BEFORE and AFTER UPDATE triggers against XML columns. In addition, you can create INSERT or DELETE triggers against tables having XML columns.

Validation of XML documents against pre-defined XML schemas is always a good practice to ensure data integrity. BEFORE triggers can automatically validate an XML document before it is being updated or inserted into columns with XML data type. If automation of XML validation is not required, you can use the WHEN clause of the BEFORE trigger against the XML column. You can set a condition so that the XML document must already be VALIDATED or that it must not be validated for the trigger to be activated.

Listing 7 shows how to create a BEFORE trigger that validates an XML document before it is inserted into the purchaseorder table. The trigger is always activated before XML documents are updated.

Note: Any trigger that references an XML schema has a dependency on that schema. Before you can reference an XML schema, it must be registered in the XML schema repository. If the XML schema the trigger depends on is later dropped from the XML schema repository, the trigger is marked inoperative.

Listing 7. Create trigger to validate data in an XML document
 CREATE TRIGGER new_order
   BEFORE INSERT ON purchaseorder
   REFERENCING NEW AS N
   FOR EACH ROW
      SET (n.porder) =  xmlvalidate(n.porder
      ACCORDING TOXMLSCHEMA URI 'http://posample.org/order.xsd')

Extending the trigger body using SQL PL statements

The syntax diagram in Listing 8 lists SQL PL statements that can be used to extend trigger bodies providing for more complex logic situations:

Listing 8. General Syntax Diagram for CREATE TRIGGER, continued
General Syntax Diagram for CREATE TRIGGER cont.
SQL-procedure-statement

|--+-CALL----------------------------------------------+--------|
   +-Compound SQL (Dynamic)----------------------------+
   +-FOR-----------------------------------------------+
   +-+-----------------------------------+--fullselect-+
   | |       .-,-----------------------. |             |
   | |       V                         | |             |
   | '-WITH----common-table-expression-+-'             |
   +-GET DIAGNOSTICS-----------------------------------+
   +-IF------------------------------------------------+
   +-INSERT--------------------------------------------+
   +-ITERATE-------------------------------------------+
   +-LEAVE---------------------------------------------+
   +-MERGE---------------------------------------------+
   +-searched-delete-----------------------------------+
   +-searched-update-----------------------------------+
   +-SET Variable--------------------------------------+
   +-SIGNAL--------------------------------------------+
   '-WHILE---------------------------------------------'

Listing 9 illustrates how you can embed a CALL statement from within the trigger body, invoking a stored procedure. In this example, the parameters are passed by value to the called procedure write_audit. This simple example demonstrates the value of invoking stored procedures from a trigger. You should now be able to extend the trigger body and implement more complex logic using SQL PL statements.

Listing 9. Invoking a stored procedure from within a trigger
CREATE PROCEDURE write_audit( IN p_empno   CHAR(6),
                              IN p_txt     CHAR(6),
                              IN p_salary  DECIMAL(9,2),
                              IN p_user    CHAR(8),
                              IN p_curtime TIMESTAMP )
BEGIN
  INSERT INTO audit_emp  VALUES ( p_empno, p_txt, p_salary, p_user, p_curtime )
END

CREATE TRIGGER new_emp
  AFTER INSERT ON employee
  REFERENCING NEW AS n
  FOR EACH ROW
  CALL write_audit( n.empno, 'Insert', 0.0, current user, current timestamp)

Listing 10 illustrates how you can enforce business rules on data being inserted into the database. This example establishes business rules for the empprojact table. The example references the emendate column and enforces several business rules on the data being inserted into the database. It also assigns the SQLSTATE the value of 90000 to Business Rule Two. This allows you to force the invocation of a handler. The SIGNAL statement allows you to force an error or warning through the setting of SQLSTATE.

Listing 10. BEFORE INSERT trigger Using SQL PL
CREATE TRIGGER business_rules
BEFORE INSERT ON empprojact
REFERENCING NEW AS n
FOR EACH ROW
  BEGIN ATOMIC
  -- Business Rule One (Project ending date Can't be NULL)
  IF (n.emendate IS NULL) THEN
     SET n.emendate = CURRENT date;
  END IF;

  -- Business Rule Two (Project ending date Can't end in last month of the year)
  IF (n.emendate BETWEEN '2009-12-01' AND '2009-12-31') THEN
     SIGNAL SQLSTATE '90000'
        SET MESSAGE_TEXT = 'Business Rule violation - 90000';
  END IF;

  END!

Conclusion

This tutorial gave an overview of how triggers are created and how, why, and when they are applied to address various business rules and requirements. This should now give you a solid base understanding of triggers that is key in preparing for the IBM Certified Solution Developer - DB2 9.5 SQL Procedure Developer exam (Exam 735).

Resources

Learn

Get products and technologies

  • DB2 Express-C: Now you can use DB2 for free. Download DB2 Express-C, a free version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edition and provides a solid base to build and deploy applications.
  • Build your next development project with IBM trial software, available for download directly from developerWorks.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=373118
ArticleTitle=DB2 9.5 SQL Procedure Developer exam 735 prep, Part 4: Triggers
publish-date=02262009