今天讨论一个比较实际的课题：就是能不能在trigger中定义 procedure ？
同事在跑的时候报错，但是白皮书上面确实支持，那么问题出在哪里呢？我们来吧基本的素材准备一下：
一个什么是procedure？
Stored procedures are useful when your application needs to execute multiple
remote SQL statements, access tables from a dynamic SQL environment where
table privileges for the application are undesirable, access host variables for which
you want to guarantee security and integrity, or create a result set to return to the
client application.
说了这么多都是废话，无非就是你想进行权限控制，但是不想把权限写到application中区，这是就需要将sQL statement 打包
根据实现以及是否进行编译（偶猜），分为三类：
native SQL procedure
A procedure whose body is written in SQL and for which DB2 does not
generate an associated C program.（不编译）
external SQL procedure
A procedure whose body is written in SQL and DB2 supports by
generating an associated C program.(编译)
external stored procedures
A procedure that is written in a host language.（如cobol)
书上紧跟着来了这么一段：
You can create one of the following types of stored procedures:
SQL procedures
The procedure is written exclusively in SQL statements. The body of an
SQL procedure is written in the SQL procedural language. SQL procedures
can be either external SQL procedures or native SQL procedures.
External stored procedures
External stored procedures are written in a host language. The source code
for an external stored procedure is separate from the definition for the
stored procedure.
看了下来意思是说，细分的是3累，大概是两类，sql于native 下面谈到我们的菜了：
native SQL procedures
Native SQL procedures are procedures whose body is written in SQL, and
DB2 does not generate an associated C program. Native SQL procedures
have the following advantages
v You can create them in one step.
v They usually perform better than external SQL procedures.
v They support more capabilities, such as nested compound statements,
than external SQL procedures.
v DB2 can manage multiple versions of these procedures for you.
Starting in Version 9.1, all SQL procedures that are created without the
FENCED or EXTERNAL options in the CREATE PROCEDURE statement
are native SQL procedures.
external SQL procedures
External SQL procedures are procedures whose body is written in SQL, but
DB2 supports them by generating an associated C program for each
procedure. All SQL procedures that were created prior to Version 9.1 are
external SQL procedures. Starting in Version 9.1, you can create an external
SQL procedure by specifying FENCED or EXTERNAL in the CREATE
PROCEDURE statement.
说了这么多，其实就是，z/os是支持带sql的
举个例子：
-
CREATE PROCEDURE UPDATESALARY2
-
(IN EMPNUMBR CHAR(6),
-
IN RATING INT)
-
LANGUAGE SQL
-
MODIFIES SQL DATA
-
CASE RATING
-
WHEN 1 THEN
-
UPDATE CORPDATA.EMPLOYEE
-
SET SALARY = SALARY * 1.10, BONUS = 1000
-
WHERE EMPNO = EMPNUMBR;
-
WHEN 2 THEN
-
UPDATE CORPDATA.EMPLOYEE
-
SET SALARY = SALARY * 1.05, BONUS = 500
-
WHERE EMPNO = EMPNUMBR;
-
ELSE
-
UPDATE CORPDATA.EMPLOYEE
-
SET SALARY = SALARY * 1.03, BONUS = 0
-
WHERE EMPNO = EMPNUMBR;
-
END CASE
接下来，我们讨论：trigger ：
A trigger is a set of SQL statements that execute when a certain event occurs in a
table. Use triggers to control changes in DB2 databases. Triggers are more powerful
than constraints, because they can monitor a broader range of changes and
perform a broader range of actions than constraints can.
意思是说当特定的事件发生时，会触发trigger定义的action，而且事件都是提前定义好的
举例说明：
-
1
-
CREATE TRIGGER REORDER
-
2 3 4
-
AFTER UPDATE OF ON_HAND, MAX_STOCKED ON PARTS
-
5
-
REFERENCING NEW AS N_ROW
-
6
-
FOR EACH ROW MODE DB2SQL
-
7
-
WHEN (N_ROW.ON_HAND < 0.10 * N_ROW.MAX_STOCKED)
-
8
-
BEGIN ATOMIC
-
CALL ISSUE_SHIP_REQUEST(N_ROW.MAX_STOCKED -
-
N_ROW.ON_HAND,
-
N_ROW.PARTNO);
-
END
解释如下：
1 Trigger name (REORDER)
2 Trigger activation time (AFTER)
3 Triggering event (UPDATE)
4 Subject table name (PARTS)
5 New transition variable correlation name (N_ROW)
6 Granularity (FOR EACH ROW)
7 Trigger condition (WHEN...)
8 Trigger body (BEGIN ATOMIC...END;)
Triggering event:
Every trigger is associated with an event. A trigger is activated when the triggering
event occurs in the subject table. The triggering event is one of the following SQL
operations:
v insert
v update
v delete
A triggering event can also be an update or delete operation that occurs as the
result of a referential constraint with ON DELETE SET NULL or ON DELETE
CASCADE.
Triggers are not activated as the result of updates made to tables by DB2 utilities,
with the exception of the LOAD utility when it is specified with the RESUME YES
and SHRLEVEL CHANGE options. (了解一下）
针对没有每一个event，触发的条件的粒度可以行级别的或是 语句级别的：
The granularity values are:
v FOR EACH ROW
The trigger is activated once for each row that DB2 modifies in the subject table.
If the triggering SQL statement modifies no rows, the trigger is not activated.
However, if the triggering SQL statement updates a value in a row to the same
value, the trigger is activated. For example, if an UPDATE trigger is defined on
table COMPANY_STATS, the following SQL statement will activate the trigger.
UPDATE COMPANY_STATS SET NBEMP = NBEMP;
v FOR EACH STATEMENT
The trigger is activated once when the triggering SQL statement executes. The
trigger is activated even if the triggering SQL statement modifies no rows.
接着我们看一个比较有意思的问题,就是变量传递：
Transition variables:
When you code a row trigger, you might need to refer to the values of columns in
each updated row of the subject table. To do this, specify transition variables in the
REFERENCING clause of your CREATE TRIGGER statement. The two types of
transition variables are:
v Old transition variables, specified with the OLD transition-variable clause, capture
the values of columns before the triggering SQL statement updates them. You
can define old transition variables for update and delete triggers.
v New transition variables, specified with the NEW transition-variable clause,
capture the values of columns after the triggering SQL statement updates them.
You can define new transition variables for update and insert triggers.
Old和New的区别在于你引用的值是trigger执行前还是trigger执行后的值，同样你的trigger的granular 是statement level是，对应需要的变量就是row set，可以这么定义：
Transition tables:
If you want to refer to the entire set of rows that a triggering SQL statement
modifies, rather than to individual rows, use a transition table. Like transition
variables, transition tables can appear in the REFERENCING clause of a CREATE
TRIGGER statement. Transition tables are valid for both row triggers and statement
triggers. The two types of transition tables are:
v Old transition tables, specified with the OLD TABLE transition-table-name clause,
capture the values of columns before the triggering SQL statement updates
them. You can define old transition tables for update and delete triggers.
v New transition tables, specified with the NEW TABLE transition-table-name
clause, capture the values of columns after the triggering SQL statement updates
them. You can define new transition variables for update and insert triggers.
/******************************************************************/
上面说了这么，都是准备工作，根本的目的是trigger能不call procedure
这就是 trigger的action body
具体的描述如下：
Trigger body:
In the trigger body, you code the SQL statements that you want to execute
whenever the trigger condition is true. If the trigger body consists of more tha
one statement, it must begin with BEGIN ATOMIC and end with END. You ca
include host variables or parameter markers in your trigger body. If the trigger
body contains a WHERE clause that references transition variables, the compar
operator cannot be LIKE.
重点来了哈~~~~~~~~~~~~~~~~~~```
Invoking stored procedures and user-defined functions from
triggers
A trigger body can include only SQL statements. To perform actions or use logic
that is not available in SQL statements, create user-defined functions or
stored
procedures that can be invoked from within the trigger body.
Because a before trigger must not modify any table, functions and procedures that
you invoke from a trigger cannot include INSERT, UPDATE, DELETE, or MERGE
statements that modify the subject table.
最好我们得出的结论就是：
／＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊／
o
invoke a stored procedure from a trigger, use a CALL statement. The
parameters of this stored procedure call must be constants, transition variables,
table locators, or expression
／＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊／
明天在测试环境测一把，感兴趣的筒子也可以试试－－－本来打算试试，可惜环境不具备！
－－－－－－后记
流水帐是的谢了这么多，主要的目的，把自己的思路理一下，以及其中自己认为需要注意的知识点，
其它无甚～～～～～～～～～
ref：
for
