Transcript dad-it407-11-L13

Triggers and Stored Procedures
in DB
1
Objectives
• Learn what triggers and stored
procedures are
• Learn the benefits of using them
• Learn how DB2 implements triggers and
stored procedures
• Learn how to create and call a stored
procedure1
1
To be revisited when we have migrated to v. 7.
2
What is a Trigger?
• A trigger is a specialized program that is not
called directly, but is event-driven.
• When a data modification statement, such as
an insert or an update occurs, a trigger is
executed, or “fired”, which may make other
database updates or call a stored procedure.
• A trigger is not directly called or executed.
After being created, it is always executed
when its firing event occurs.
3
Why Use Triggers?
• Support data integrity – if a change to one
column dictates a change to another, a trigger
ensures they always stay in sync.
• Simplify scheduling – if an action needs to
happen every time a particular column is
updated, the trigger avoids having to
schedule it.
• Support complex business rules – having
business rules in the database ensures
everyone uses the same logic to accomplish
the same process.
4
Timing Considerations for Triggers
• DB2 supports “BEFORE” and “AFTER” triggers. A BEFORE
trigger occurs before the database event that causes it to fire.
An AFTER trigger occurs after the database event.
• A trigger can fire once for a particular database event, or once
per affected row. These are called statement-level triggers or
row-level triggers and are defined using the FOR EACH
STATEMENT or FOR EACH ROW clause.
5
Trigger Examples
CREATE TRIGGER CRITICAL_PROJECT
AFTER UPDATE OF PROJ_END_DATE ON PROJ
REFERENCING NEW AS NEWPROJ
FOR EACH ROW
WHEN (NEWPROJ.PROJ_END_DATE < CURRENT DATE + 14
DAYS)
BEGIN ATOMIC
CALL CRITPROJ (NEWPROJ.PROJNO);
END
Here, when a project end date is updated and is within the next 2
weeks, a critical project routine is called. This could print an alert
for management, put a rush on a supply order, or whatever is
needed for the business.
Note the project number can be referenced and passed to the stored
procedure.
6
Another Trigger Example
CREATE TRIGGER TOT_COMP
AFTER UPDATE OF SALARY, BONUS, COMM ON EMP
REFERENCING NEW AS INSERTED, OLD AS DELETED
FOR EACH ROW
WHEN (INSERTED.SALARY <> DELETED.SALARY
OR INSERTED.BONUS <> DELETED.BONUS
OR INSERTED.COMM <> DELETED.COM)
BEGIN ATOMIC
UPDATE EMP_SALARY
SET TOT_COMP = INSERTED.SALARY + INSERTED.BONUS +
INSERTED COMM
WHERE EMP_SALARY.EMPNO = INSERTED.EMPNO;
END
Here, when an employee’s salary, bonus or commission is changed on the emp
table, his total compensation is updated accordingly on the emp_salary table.
7
What is a Stored Procedure?
• A stored procedure is a specialized program that is
stored in the relational database management
system instead of in an external code library.
• It may access and/or modify data in one or more
tables, but it is not physically associated with a table,
or any other object.
• A stored procedure must be invoked, or called, before
it can be executed. It is not event-driven.
• A major motivating reason for using stored
procedures is to move SQL code from a client to the
database server. One client request to a stored
procedure can replace many SQL calls, reducing
network traffic and speeding up processing.
8
Why use Stored Procedures?
• Performance: In a client/server or internet
environment, stored procedures can reduce network
traffic because multiple SQL statements can be
invoked with a single stored procedure. Only the
request and the final results need to be sent across
the network.
• Reusability: Stored procedures allow code to reside
in one place, the database server. Multiple client
programs can call the procedures as needed, without
duplicating code.
• Consistency: business rules are implemented only
one way, not interpreted differently by each
programmer.
• Maintenance: Code changes are only required in
one place.
9
Why use Stored Procedures, cont.
• Data Integrity: Stored procedures can perform
column validations, so if all applications use the same
procedure, the data is always validated.
• Security: If a given group of users requires access
to specific data items, you can provide a stored
procedure that returns just those items. You can then
grant access to call the stored procedure, without
giving those users any additional authorization to the
underlying database objects.
• Database protection: Stored procedures run in a
separate address space from the database engine,
eliminating the possibility of users corrupting the
DBMS.
10
Parameters
•
•
•
Parameters allow data to be passed to and received
from a stored procedure.
They are used similarly to how they are used to call
a subroutine. For example, to call the DATECALC
program, you need to send a date, and a few
switches to indicate what you want back – do you
want the date converted to another format? The
day of the week? The program returns the results
to you in another parameter.
Three types of parameters may be defined: IN
(input parameter), OUT (output parameter) and
INOUT (parameter that is used for both input and
output).
11
Preparing a Stored Procedure for
Execution
• After a stored procedure’s source statements have
been created, they are precompiled, compiled, linkedited and bound, producing an executable program
and a DB2 package.
• If the stored procedure is written in SPL, it is
converted to C source statements and compiled into
a C executable program.
• Stored procedures are usually written as reentrant
code that will be started with the command START
PROCEDURE(procname)
• The procedure will stay resident and available to be
called until a STOP command is issued.
12
Stored Procedure Example
CREATE PROCEDURE UPDATE_SALARY
(IN EMPLOYEE_NUMBER CHAR(10),
IN RATE DECIMAL(6.2))
LANGUAGE SQL
COMMIT ON RETURN YES
IF RATE <= 0.50
THEN UPDATE EMP
SET SALARY = SALARY + (SALARY * RATE)
WHERE EMPNO = EMPLOYEE_NUMBER;
ELSE UPDATE EMP
SET SALARY = SALARY + (SALARY * 0.50)
WHERE EMPNO = EMPLOYEE_NUMBER;
END IF
Here, the procedure accepts an employee number and rate. The
employee’s salary is increased by the rate, and an IF THEN
ELSE construct is used to ensure the raise is not greater than
50%.
13