db2 触发器
1) DB2触发器有三种
NO CASCADE BEFORE；AFTER；INSTEAD OF
我想实现对一个表插入前先插入另一个表的记录
CREATE TRIGGER default_time
NO CASCADE BEFORE INSERT ON inventory
REFERENCING NEW AS n
FOR EACH ROW
MODE DB2SQL
INSERT INTO sold
VALUES (n.product_ID, n.daysold, o.quantity - n.quantity)
结果居然显示：
SQL0797N用不受支持的触发SQL语句定义了触发器，SQLSTATE=42987
BEFORE 触发器还可以包括下列已触发的 SQL 语句：
- 调用不修改 SQL 数据的 CALL 语句
- 全查询
- 赋值语句
但是，BEFORE 触发器中触发的 SQL 语句不能包括：
- 修改 SQL 数据的表函数
- 嵌套的 DELETE、INSERT、MERGE 或 UPDATE 语句
2) 在CLP中创建存储过程时，若使用
db2 create procedure p_salary (in pid int, out pname varchar(30) )
language sql
p1: begin
declare tempname varchar(10);
select empname into tempname
from salarytable
where empid=pid;
set pname=tempname||'a'||tempname;
end p1
总是返回：
SQLSTATE 42601 ''a''不是内部命令，也不是可运行的程序或批处理文件。''tempname''不是内部命令，也不是可运行的程序或批处理文件。
好像是连接符||出了问题
如果把最后一句改为：
set pname=tempname就胜利通过
||不行的话，用concat试试
3) DB2的触发器和ORACLE有什么不一样的地方
Oracle 的限制
PHP code:
Maximum Trigger Size
The size of a trigger cannot be more than 32K.
Trigger Restrictions on Mutating Tables
A mutating table is a table that is being modified by an UPDATE, DELETE, or INSERT statement, or a table that might be updated by the effects of a DELETE CASCADE constraint.
The session that issued the triggering statement cannot query or modify a mutating table. This restriction prevents a trigger from seeing an inconsistent set of data.
This restriction applies to all triggers that use the FOR EACH ROW clause.
db2 可以2M大
db2 没有mutating tables的限制，比如，你完全可以有下面这样的trigger
PHP code:
CREATE TRIGGER verify_credit
NO CASCADE BEFORE INSERT ON orders_t
REFERENCING NEW AS n
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
DECLARE current_due DECIMAL(10,2) DEFAULT 0;
DECLARE credit_line DECIMAL(10,2);
/*
* get the customer's credit line
*/
SET credit_line = (SELECT credit
FROM customer_t c
WHERE c.cust_id=n.cust_id);
-- sum up the current amount currently due
FOR ord_cursor AS
SELECT quantity, price
FROM orders_t ord
WHERE ord.cust_id=n.cust_id AND
status not IN ('COMPLETED','CANCELLED') DO
SET current_due = current_due +
(ord_cursor.price * ord_cursor.quantity);
END FOR;
IF (current_due + n.price * n.quantity) > credit_line THEN
SIGNAL SQLSTATE '80000' ('Order Exceeds credit line');
END IF;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/13165828/viewspace-607158/ ，如需转载，请注明出处，否则将追究法律责任。
