Defining other business rules

DB2 provides two additional mechanisms that you can use to enforce your business rules—triggers and user-defined functions.

Defining triggers

You read about triggers in “Triggers” on page 57.

Triggers automatically execute a set of SQL statements whenever a specified event occurs. These statements validate and edit database changes, read and modify the database, and invoke functions that perform operations inside and outside the database. A trigger is a powerful mechanism. You can use triggers to define and enforce business rules that involve different states of the data.

Example: Assume that the majority of your organization's salary increases are less than or equal to 10 percent. Assume also that you need to receive notification of any attempts to increase a value in the salary column by more than that amount. To enforce this requirement, DB2 compares the value of a salary before a salary increase to the value that would exist after a salary increase. You can use a trigger in this case. Whenever a program updates the salary column, DB2 activates the trigger. In the triggered action, you can specify that DB2 is to perform the following actions:

• Update the value in the salary column with a valid value, rather than preventing the update altogether.

• Notify an administrator of the attempt to make an invalid update.

As a result of using a trigger, the notified administrator can decide whether to override the original salary increase and allow a larger-than-normal salary increase.

Recommendation: For rules that involve only one condition of the data, consider using referential constraints and check constraints rather than triggers.

Triggers also move the application logic that is required to enforce business rules into the database, which can result in faster application development and easier maintenance. In the previous example, which limits salary increases, the logic is in the database, rather than in an application. DB2 checks the validity of the changes that any application makes to the salary column. In addition, if the logic ever changes (for example, to allow 12 percent increases), you don't need to change the application programs.

Triggers are optional. You define triggers by using the CREATE TRIGGER statement.

Defining user-defined functions

You read about user-defined functions in “Using user-defined functions” on page 135.

User-defined functions can be sourced, external, or SQL functions. Sourced means that they are based on existing functions. External means that users develop them. SQL means that the function is defined to the database by only SQL statements.

External user-defined functions can return a single value or a table of values.

• External functions that return a single value are called user-defined scalar functions.

• External functions that return a table are called user-defined table functions.

User-defined functions, like built-in functions or operators, support the manipulation of distinct types. “Defining and using distinct types” on page 232 introduces distinct types.

The following two examples demonstrate how to define and use both a user-defined function and a distinct type.

Example: Suppose that you define a table called EUROEMP. One column of this table, EUROSAL, has a distinct type of EURO, which is based on DECIMAL(9,2). You cannot use the built-in AVG function to find the average value of EUROSAL because AVG operates on built-in data types only. You can, however, define an AVG function that is sourced on the built-in AVG function and accepts arguments of type EURO:

CREATE FUNCTION AVG(EURO)
 RETURNS EURO
 SOURCE SYSIBM.AVG(DECIMAL);

Example: You can then use this function to find the average value of the EUROSAL column:

SELECT AVG(EUROSAL) FROM EUROEMP;

The next two examples demonstrate how to define and use an external user-defined function.

Example: Suppose that you define and write a function, called REVERSE, to reverse the characters in a string. The definition looks like this:

CREATE FUNCTION REVERSE(CHAR)
 RETURNS CHAR
 EXTERNAL NAME 'REVERSE'
 PARAMETER STYLE DB2SQL
 LANGUAGE C;

Example: You can then use the REVERSE function in an SQL statement wherever you would use any built-in function that accepts a character argument. For example:

SELECT REVERSE(:CHARSTR)
 FROM SYSDUMMY1;

Although you cannot write user-defined aggregate functions, you can define sourced user-defined aggregate functions that are based on built-in aggregate functions. This capability is useful in cases where you want to refer to an existing user-defined function by another name or to pass in a distinct type.

The next two examples demonstrate how to define and use a user-defined table function.

Example: You can define and write a user-defined table function that users can invoke in the FROM clause of a SELECT statement. For example, suppose that you define and write a function called BOOKS. This function returns a table of information about books on a given subject. The definition looks like this:

CREATE FUNCTION BOOKS            (VARCHAR(40))
   RETURNS TABLE  (TITLE_NAME     VARCHAR(25),
                   AUTHOR_NAME    VARCHAR(25),
                   PUBLISHER_NAME VARCHAR(25),
                   ISBNNO         VARCHAR(20),
                   PRICE_AMT     DECIMAL(5,2),
                   CHAP1_TXT        CLOB(50K))
   LANGUAGE COBOL
   PARAMETER STYLE DB2SQL
   EXTERNAL NAME BOOKS;

Example: You can then include the BOOKS function in the FROM clause of a SELECT statement to retrieve the book information. For example:

SELECT B.TITLE_NAME, B.AUTHOR_NAME, B.PUBLISHER_NAME,
B.ISBNNO
 FROM TABLE(BOOKS('Computers')) AS B
 WHERE B.TITLE_NAME LIKE '%COBOL%';