db2_触发器
1、触发器类型
1）BEFORE 触发器：在对表插入或更新之前执行该触发器，允许使用CALL 和 SIGNAL SQL 语句；
2）BEFORE DELETE 触发器：在删除操作之前执行该触发器；
3）AFTER 触发器：在更新、插入或删除操作之后执行。该触发器用于更新反映表间关系和一致性的其他表中的数据，还用于确保数据完整性。AFTER 触发器通常用于在特定情况下向用户生成报警；
4）INSTEAD OF 触发器：该触发器支持对不支持插入、更新和删除操作的视图执行这些操作；
2、创建触发器所需的权限
1）使用触发器的模式ID必须拥有以下权限之一：
i)对定义 BEFORE 或 AFTER 触发器的表拥有 ALTER 权限；
ii)对定义 INSTEAD OF 触发器的视图拥有 CONTROL 权限；
iiI)对定义 INSTEAD OF 触发器的视图拥有所有权；
iv)对定义触发器的表或视图的模式拥有 ALTERIN 权限；
v)SYSADM 或 DBADM 授权；
2）以及以下任意一种权限：
i)IMPLICIT_SCHEMA 数据库授权（如果触发器的隐式或显式模式名称不存在）；
ii)对模式的 CREATEIN 权限（如果触发器的模式名称引用现有的模式）；
3）假定授权 ID 没有 SYSADM 和 DBADM 权限并且触发器存在，此 ID 必须拥有以下所有权限：
i)对定义触发器的表拥有 SELECT 权限 — 用于转换变量和/或表；
ii)对在触发的操作条件中引用的任意表或视图的 SELECT 权限；
iii)调用触发的指定 SQL 语句所需的权限；
3、创建触发器语句
1）语法
.-NO CASCADE-.
>>-CREATE TRIGGER--trigger-name--+-+------------+--BEFORE-+----->
+-AFTER------------------+
'-INSTEAD OF-------------'
>--+-INSERT--------------------------+--ON--+-table-name-+------>
+-DELETE--------------------------+ '-view-name--'
'-UPDATE--+---------------------+-'
| .-,-----------. |
| V | |
'-OF----column-name-+-'
>--+------------------------------------------------------------------+-->
| .-------------------------------------------------. |
| V (1) (2) .-AS-. | |
'-REFERENCING------------------+-OLD--+----+--correlation-name-+-+-'
| .-AS-. |
+-NEW--+----+--correlation-name-+
| .-AS-. |
+-OLD TABLE--+----+--identifier-+
| .-AS-. |
'-NEW TABLE--+----+--identifier-'
>--+-FOR EACH ROW--------------+--| triggered-action |--------->
| (3) |
'--------FOR EACH STATEMENT-'
triggered-action
|--+-------------------------------------+---------------------->
| (4) |
'--------WHEN--(--search-condition--)-'
2）触发器有三个主要组件：
i)触发器事件；
ii)触发器条件；
iii)触发器操作；
3）示例：
i)简单的 AFTER INSERT 触发器
CREATE TRIGGER new_emp
AFTER INSERT ON employee
REFERENCING NEW AS n
FOR EACH ROW
INSERT INTO audit_emp VALUES (n.empno, 'Insert',0.0, current user, current timestamp)
ii)简单的 AFTER DELETE 触发器
CREATE TRIGGER purge_emp
AFTER DELETE ON employee
REFERENCING OLD AS o
FOR EACH ROW
INSERT INTO audit_emp VALUES (o.empno, 'Delete',0.0, current user, current timestamp)
iii)简单的 AFTER UPDATE 触发器
CREATE TRIGGER update_emp
AFTER UPDATE OF salary ON employee
REFERENCING OLD AS o NEW AS n
FOR EACH ROW
WHEN (n.salary <> o.salry)
INSERT INTO audit_emp VALUES (o.empno,'Update',n.salary,current user, current timestamp)
iv)简单的 BEFORE UPDATE 触发器
CREATE TRIGGER update_bemp
BEFORE UPDATE ON employee
REFERENCING OLD AS o NEW AS n
FOR EACH ROW
WHEN (n.salary = 60000.00)
SET n.salary = 75000.00)
4、触发器的高级用法
1）INSTEAD OF 触发器
--示例
CREATE TABLE "DB2INST1"."EMPLOYEES"
(
"EMPNO" CHAR(6) NOT NULL ,
"FIRSTNME" VARCHAR(12) NOT NULL ,
"LASTNAME" VARCHAR(15) NOT NULL ,
"PHONENO" CHAR(4) ,
"SALARY" DECIMAL(9,2)
)
CREATE VIEW employeev AS
SELECT empno, firstnme, lastname, phoneno
FROM employees
CREATE TRIGGER new_emp1
INSTEAD OF INSERT ON employeev
REFERENCING NEW AS n
FOR EACH ROW
INSERT INTO employees VALUES (n.empno, n.firstnme, n.lastname, n.phoneno, 0)
2）用触发器处理 XML 数据
--示例
CREATE TRIGGER new_order
BEFORE INSERT ON purchaseorder
REFERENCING NEW AS N
FOR EACH ROW
SET (n.porder) = xmlvalidate(n.porder
ACCORDING TOXMLSCHEMA URI 'http://posample.org/order.xsd')
3）使用 SQL PL 语句扩展触发器主体
i)语法
General Syntax Diagram for CREATE TRIGGER cont. SQL-procedure-statement |--+-CALL----------------------------------------------+--------| +-Compound SQL (Dynamic)----------------------------+ +-FOR-----------------------------------------------+ +-+-----------------------------------+--fullselect-+ | | .-,-----------------------. | | | | V | | | | '-WITH----common-table-expression-+-' | +-GET DIAGNOSTICS-----------------------------------+ +-IF------------------------------------------------+ +-INSERT--------------------------------------------+ +-ITERATE-------------------------------------------+ +-LEAVE---------------------------------------------+ +-MERGE---------------------------------------------+ +-searched-delete-----------------------------------+ +-searched-update-----------------------------------+ +-SET Variable--------------------------------------+ +-SIGNAL--------------------------------------------+ '-WHILE---------------------------------------------'
ii)从触发器中调用存储过程
CREATE PROCEDURE write_audit( IN p_empno CHAR(6),
IN p_txt CHAR(6),
IN p_salary DECIMAL(9,2),
IN p_user CHAR(8),
IN p_curtime TIMESTAMP )
BEGIN
INSERT INTO audit_emp VALUES ( p_empno, p_txt, p_salary, p_user, p_curtime )
END
CREATE TRIGGER new_emp
AFTER INSERT ON employee
REFERENCING NEW AS n
FOR EACH ROW
CALL write_audit( n.empno, 'Insert', 0.0, current user, current timestamp)
iii)使用 SQL PL 的 BEFORE INSERT 触发器
CREATE TRIGGER business_rules
BEFORE INSERT ON empprojact
REFERENCING NEW AS n
FOR EACH ROW
BEGIN ATOMIC
-- Business Rule One (Project ending date Can't be NULL)
IF (n.emendate IS NULL) THEN
SET n.emendate = CURRENT date;
END IF;
-- Business Rule Two (Project ending date Can't end in last month of the year)
IF (n.emendate BETWEEN '2009-12-01' AND '2009-12-31') THEN
SIGNAL SQLSTATE '90000'
SET MESSAGE_TEXT = 'Business Rule violation - 90000';
END IF;
END
----------------------------------------------------------
《高性能SQL调优精要与案例解析》
blog1：http://www.cnblogs.com/lhdz_bj
blog2：http://blog.itpub.net/8484829
blog3：http://blog.csdn.net/tuning_optmization
