121.Oracle数据库SQL开发之 PLSQL编程——触发器
欢迎转载，转载请标明出处：http://blog.csdn.net/notbaron/article/details/50086995
触发器是当特定的SQL DML语句，例如INSERT,UPDATE或DELETE语句再特定的数据库表上运行时，由数据库自动运行的过程。触发器对于实现表中某些列值的高级变更审计等功能非常有用。
1. 触发器运行的时机
触发器可以在DML语句运行之前和之后被激活。同时，由于DML语句可能同时作用于多行，所以触发器的过程代码可能在作用的每一行上都运行一次，这样的触发器称为行级触发器（row-level trigger）,也可能只在所有行运行一次，称为语句级触发器（statement-level trigger）.
行级触发器和语句级触发器还有另一个差别：当UPDATE语句在某个列上激活行级触发器时，这个触发器可以同时访问该列的原值和新值。
2. 设置示例触发器
触发器对于实现表中某些列值的高级变更审计等功能非常有用。
先创建一个表如下：
CREATE TABLE product_price_audit (
product_idINTEGER
CONSTRAINTprice_audit_fk_products
REFERENCESproducts(product_id),
old_price NUMBER(5, 2),
new_price NUMBER(5, 2)
);
3. 创建触发器
创建触发器命令如下：CREATE TRIGGER语句。
CREATE TRIGGER before_product_price_update
BEFORE UPDATE OF price
ON products
FOR EACH ROW WHEN (new.price < old.price * 0.75)
BEGIN
dbms_output.put_line('product_id= ' || :old.product_id);
dbms_output.put_line('Old price = ' || :old.price);
dbms_output.put_line('New price = ' || :new.price);
dbms_output.put_line('The price reduction is more than 25%');
-- insert rowinto the product_price_audit table
INSERT INTOproduct_price_audit (
product_id,old_price, new_price
) VALUES (
:old.product_id, :old.price, :new.price
);
END before_product_price_update;
/
在products表的price列更新之前激活。
l 其中BEFOREUPDATE OF子句指定触发器在更新价格列之前激活
l FOR EACH ROW表名这是一个行级触发器
l 触发器条件是（new.price<old.price*0.75）
l 在触发器中，可以通过:old和:new别名访问列的原值和新值
l 触发器代码先显示产品ID、新旧价格和表示价格降低幅度超过25%的消息，然后向product_price_audit表中增加一行记录，其中包括产品ID和新旧价格。
4. 激活触发器
SET SERVEROUTPUT ON
调用如下：
store@PDB1> update products set price=price*0.7where product_id in (5,10);
product_id = 5
Old price = 49.99
New price = 34.99
The price reduction is more than 25%
product_id = 10
Old price = 15.99
New price = 11.19
The price reduction is more than 25%
2 rows updated.
查看如下：
store@PDB1> select * from product_price_auditorder by product_id;
PRODUCT_ID OLD_PRICE NEW_PRICE
---------- ---------- ----------
5 49.99 34.99
10 15.99 11.19
5. 获取有关触发器的信息
从user_triggers视图中可以后的触发器的信息。
下面这个例子从user_triggers中检查before_product_price_update触发器的详细信息
store@PDB1> selecttrigger_name,trigger_type,triggering_event,table_ownerbase_object_type,table_name,referencing_names,when_clause,status,description,action_type,trigger_bodyfrom user_triggers where trigger_name='BEFORE_PRODUCT_PRICE_UPDATE';
TRIGGER_NAME
----------------------------------------------------------------------------------------------------
TRIGGER_TYPE
----------------
TRIGGERING_EVENT
----------------------------------------------------------------------------------------------------
BASE_OBJECT_TYPE
----------------------------------------------------------------------------------------------------
TABLE_NAME
----------------------------------------------------------------------------------------------------
REFERENCING_NAMES
----------------------------------------------------------------------------------------------------
WHEN_CLAUSE
----------------------------------------------------------------------------------------------------
STATUS
--------
DESCRIPTION
----------------------------------------------------------------------------------------------------
ACTION_TYPE TRIGGER_BODY
-------------------------------------------------------------------------------------------
BEFORE_PRODUCT_PRICE_UPDATE
BEFORE EACH ROW
UPDATE
STORE
PRODUCTS
REFERENCING NEW AS NEW OLD AS OLD
new.price < old.price * 0.75
ENABLED
before_product_price_update
BEFORE UPDATE OF price
ON products
FOR EACH ROW
PL/SQL BEGIN
dbms_output.put_line('product_id = ' ||:old.product_id);
dbms_output.put_line('Old price = ' ||:old.price);
dbms_output.put_line('New price = ' ||:new.price);
dbms_output.put_line('The price reductionis more than 25%');
-- insert row into theproduct_price_audit table
INSERT INTO product_price_audit (
product_id,old_price, new_price
) VALUES (
:old.product_id,:old.price, :new.price
);
END before_product_price_update;
6. 禁用和启用触发器
禁用触发器，如下：
ALTER TRIGGER before_product_price_updateDISABLE;
触发如下：
ALTER TRIGGER before_product_price_updateENABLE;
7. 删除触发器
DROP TRIGGER语句用于删除触发器。
例如：
DROP TRIGGER before_product_price_update;