IBM i > ADMINISTRATOR > DB2

Multiple Events Supported in a Single SQL Trigger for IBM i


IBM is dedicated to providing the most innovative database solution to DB2 for i clients. It has released numerous functional enhancements that can be found at the IBM developerWorks - IBM i Technology Updates website. The IBM i Technology Updates website is a great place to visit to keep up with the latest and greatest technological enhancements for DB2 for i, as well as learn about future enhancements that will be released to the platform.

One of the most recent DB2 for i enhancements is the addition of multiple events supported in a single SQL trigger. This enhancement allows a database programmer to combine several SQL triggers, which have different firing actions, into one consolidated trigger making management of database triggers much simpler. Multiple events supported in a single SQL trigger is available on IBM i 7.1 through DB Group PTF SF99701 Level 23 or higher.

DB2 for i native triggers already support INSERT, UPDATE and DELETE trigger events within a single program. Now, SQL trigger programs have this capability. A single trigger can be defined to handle one, two or all of the INSERT, UPDATE and DELETE event,s which would have required multiple triggers previously. Thus, SQL developers can greatly reduce the number of triggers and simplify trigger management and maintenance in their environment. Simplified management provides you with fewer pieces of source code to manage, fewer programs within a library, fewer programs to save and restore, etc.

The only restriction with multiple event triggers is that the events must share a common BEFORE, AFTER or INSTEAD OF trigger time. In the body of the trigger, the new INSERTING, UPDATING and DELETING predicates can be used to distinguish between the events that cause the trigger to fire. These predicates can be specified in control statements (like IF) or within any SQL statement that accepts a predicate (like SELECT or UPDATE).

Example of Combining Multiple triggers

The following example shows how easy it is to combine multiple trigger programs into a single SQL trigger.

An ACCOUNT table for storing bank account information was created with the following SQL statement:

CREATE TABLE ACCOUNT(ACCOUNT_ID INT NOT NULL, CUSTOMER_ID INT NOT NULL,
BALANCE DEC (13,2) NOT NULL, OVERDRAW_ACCT INT, ACCOUNT_TYPE CHAR(10) NOT NULL,
STATUS CHAR(1) NOT NULL)

The table has three SQL triggers to ensure the validity of the data stored within the OVERDRAW_ACCT column. The INSERT trigger is used to ensure an overdraw account is specified when a row is inserted into the table for a checking account. If the overdraw account is not specified or the overdraw account is closed, an error is issued.

	CREATE TRIGGER CHECK_FOR_OVERDRAW_ACCT 
	BEFORE INSERT ON ACCOUNT
	REFERENCING NEW AS NEW_ACCOUNT 
	FOR EACH ROW
	MODE DB2ROW
BEGIN 
IF (NEW_ACCOUNT.ACCOUNT_TYPE = ‘CHECKING’ ) THEN 
IF (NEW_ACCOUNT.OVERDRAW_ACCT IS NULL)
THEN 
SIGNAL SQLSTATE ‘78001’ (‘OVERDRAW_ACCT data required’);
ELSEIF (NEW_ACCOUNT.OVERDRAW_ACCT IN (SELECT DISTINCT(ACCOUNT_ID) 
FROM ACCOUNT WHERE NEW_ACCOUNT.OVERDRAW_ACCT = ACCOUNT_ID AND STATUS = ‘C’)) THEN SIGNAL SQLSTATE ‘78002’ (‘OVERDRAW_ACCT cannot have status of closed’); END IF; END IF; END

The UPDATE trigger is used to ensure the account being closed is not specified as an overdraw account. If the account is being closed and it’s specified as an overdraw account, an error is issued.

	CREATE TRIGGER CHECK_CLOSE_FOR_ACTIVE_OVERDRAW_ACCT 
	BEFORE UPDATE OF STATUS ON ACCOUNT
	REFERENCING OLD AS OLD_ACCOUNT NEW AS NEW_ACCOUNT
	FOR EACH ROW
	MODE DB2ROW
BEGIN 
IF (NEW_ACCOUNT.STATUS = ‘C’ AND OLD_ACCOUNT.STATUS <> ‘C’ AND OLD_ACCOUNT.ACCOUNT_ID 
IN (SELECT DISTINCT(OVERDRAW_ACCT)
FROM ACCOUNT WHERE OVERDRAW_ACCT =
OLD_ACCOUNT.ACCOUNT_ID AND STATUS <> ‘C’)) THEN SIGNAL SQLSTATE ‘78003’ (‘ACCOUNT cannot be closed—specified as an OVERDRAW_ACCT’); END IF; END

The DELETE trigger ensures the account being deleted is not specified as an overdraw account. If it is specified as an overdraw account, an error is issued.

CREATE TRIGGER CHECK_DELETE_FOR_OVERDRAW_ACCT BEFORE DELETE ON ACCOUNT
REFERENCING OLD AS OLD_ACCOUNT
FOR EACH ROW
	MODE DB2ROW
BEGIN 
IF (OLD_ACCOUNT.ACCOUNT_ID IN (SELECT DISTINCT(OVERDRAW_ACCT) FROM ACCOUNT WHERE 
OVERDRAW_ACCT = OLD_ACCOUNT.ACCOUNT_ID AND STATUS <> ‘C’)) THEN SIGNAL SQLSTATE ‘78004’
(‘ACCOUNT cannot be deleted—specified as an OVERDRAW_ACCT’); END IF; END

Since the aforementioned triggers have the same triggering time (BEFORE), they can be combined into a single SQL trigger. This is done by dropping the three original triggers and creating a single trigger that handles all three events:

DROP TRIGGER CHECK_FOR_OVERDRAW_ACCT
DROP TRIGGER CHECK_CLOSE_FOR_ACTIVE_OVERDRAW_ACCT
DROP TRIGGER CHECK_DELETE_FOR_OVERDRAW_ACCT
	
CREATE OR REPLACE TRIGGER ACCOUNT_MULTIPLE_EVENT_TRIGGER 
BEFORE INSERT OR UPDATE OF STATUS OR DELETE ON ACCOUNT 
REFERENCING NEW AS NEW_ACCOUNT OLD AS OLD_ACCOUNT 
FOR EACH ROW
MODE DB2ROW
BEGIN 
IF INSERTING THEN
IF (NEW_ACCOUNT.ACCOUNT_TYPE = ‘CHECKING’ ) THEN 
IF (NEW_ACCOUNT.OVERDRAW_ACCT IS NULL)
THEN 
SIGNAL SQLSTATE ‘78001’ (‘OVERDRAW_ACCT data required’);
ELSEIF (NEW_ACCOUNT.OVERDRAW_ACCT IN (SELECT DISTINCT(ACCOUNT_ID) 
FROM ACCOUNT WHERE NEW_ACCOUNT.OVERDRAW_ACCT = ACCOUNT_ID AND STATUS = ‘C’)) THEN SIGNAL SQLSTATE ‘78002’ (‘OVERDRAW_ACCT cannot have status of closed’); END IF; END IF; ELSEIF UPDATING THEN IF (NEW_ACCOUNT.STATUS = ‘C’ AND OLD_ACCOUNT.STATUS <> ‘C’ AND OLD_ACCOUNT.ACCOUNT_ID
IN (SELECT DISTINCT(OVERDRAW_ACCT) FROM ACCOUNT WHERE
OVERDRAW_ACCT = OLD_ACCOUNT.ACCOUNT_ID AND STATUS <> ‘C’)) THEN SIGNAL SQLSTATE ‘78003’ (‘ACCOUNT cannot be closed—specified as an OVERDRAW_ACCT’); END IF; ELSEIF DELETING THEN IF (OLD_ACCOUNT.ACCOUNT_ID IN (SELECT DISTINCT(OVERDRAW_ACCT) FROM ACCOUNT WHERE
OVERDRAW_ACCT = OLD_ACCOUNT.ACCOUNT_ID AND STATUS <> ‘C’)) THEN SIGNAL SQLSTATE ‘78004’ (‘ACCOUNT cannot be deleted—specified as an OVERDRAW_ACCT’); END IF; END IF; END

The SQL trigger program handles multiple events, so now only one source needs to be updated when a trigger program change is required.

Brett Leeser is a software engineer with the IBM i Final System Test team in Rochester, Minn.

Matt Wolfe is a software engineer with the IBM i Final System Test team in Rochester, Minn.

Marie Wilson is a software engineer with the IBM i Final System Test team in Rochester, Minn.

Michelle A. Schlicht is a staff software engineer with IBM. Michelle can be reached at schlicht@us.ibm.com.


comments powered by Disqus
Buyers Guide

Advertisement

Untangling Web Query

How metadata can reduce query and report complexity

Protection From Slow Queries

Introducing DB2 for i Adaptive Query Processing

Intelligent Queries

DB2 for i Learning Statistics Engine works smarter

IBM Systems Magazine Subscribe Box Read Now Link Subscribe Now Link iPad App Google Play Store
IBMi News Sign Up Today! Past News Letters

Advertisement