Universal Database (netDB2) Introductory Guide

Creating Views And Selecting Data

Universal Database supports several types of triggers. Triggers can be defined to be activated either before or after a DELETE, INSERT, or UPDATE operation. Each trigger includes a set of SQL statements called a triggered action that can include an optional search condition. Trigger before an INSERT, UPDATE, or DELETE statement to check for certain conditions before performing a triggering operation or to change the input values before they are stored in the table.

Wing example illustrates a use of before and after triggers. Consider an application that records and tracks changes to stock prices. The database contains two tables, CURRENTQUOTE and QUOTEHISTORY defined as:

CREATE TABLE CURRENTQUOTE (SYMBOL VARCHAR(10), QUOTE DECIMAL(5,2), STATUS VARCHAR(9)) CREATE TABLE QUOTEHISTORY (SYMBOL VARCHAR(10), QUOTE DECIMAL(5,2), TIMESTAMP TIMESTAMP)

When the QUOTE column of CURRENTQUOTE is updated using a statement such as:

UPDATE CURRENTQUOTE SET QUOTE = 68.5 WHERE SYMBOL = ©IBM©

The STATUS column of CURRENTQUOTE should be updated to reflect whether the stock is:

v Rising in value v At a new high for the year v Dropping in value v At a new low for the year v Steady in value.

This is done using the following before trigger::wq