PL/SQL学习（六）触发器

时间：2022-01-21 23:39:19

原文参考：http://plsql-tutorial.com/

创建语法：
CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
BEGIN
--- sql statements
END;
 
==创建说明：========================
  • CREATE [OR REPLACE ] TRIGGER trigger_name - 创建一个给定名字或者重写一个已存在的触发器
  • {BEFORE | AFTER | INSTEAD OF } - 表明触发器触发时间. 比如: 修改一个表之前或者之后. INSTEAD OF 用来在一个视图上面创建触发器. before 和 after 不能用来在视图上创建触发器 .
  • {INSERT [OR] | UPDATE [OR] | DELETE} - 定义触发事件. 可以使用OR关键字分隔 绑定的多个事件. 触发器将在所有绑定的这些事件发生的时候触发.
  • [OF col_name] - 这个声明在 update事件触发器中使用. 当你希望只更新某一特定列时触发器才触发时，可以使用这个声明.
  • [ON table_name] - 关联触发器绑定的表格或者视图.
  • [REFERENCING OLD AS o NEW AS n] - 伪列，用来引用被修改数据的旧/新值. 使用时的格式:old.column_name or :new.column_name，注意！在where子句中不加前缀“：”（如：old.column_name） ， old (new)也可以用其他自定义的名字代替. 插入事件中不能使用old,删除事件中不能使用new,因为他们不存在.
  • [FOR EACH ROW] - 表明是每行后都触发( i.e. a Row Level Trigger) 还是整个语句执行完才触发 (i.e.statement level Trigger).
  • WHEN (condition) - 仅在行级触发器里有效. 触发器仅当当前行满足when子句后的条件时才触发.
 
例子：
1) 创建 'product' 表和 'product_price_history' 表
CREATE TABLE product_price_history
(product_id number(5),
product_name varchar2(32),
supplier_name varchar2(32),
unit_price number(7,2) );
 
CREATE TABLE product
(product_id number(5),
product_name varchar2(32),
supplier_name varchar2(32),
unit_price number(7,2) );
2) 创建price_history_trigger触发器并执行
CREATE or REPLACE TRIGGER price_history_trigger
BEFORE UPDATE OF unit_price
ON product
FOR EACH ROW
BEGIN
INSERT INTO product_price_history
VALUES
(:old.product_id,
:old.product_name,
:old.supplier_name,
:old.unit_price);
END;
/
3) 修改 product 的记录
UPDATE PRODUCT SET unit_price = 800 WHERE product_id = 100
4)修改时在提交前执行回滚操作，触发器中的插入操作同样会回滚
 
类型：
1) 行级别触发器
2) 语句级别触发器
 
触发器操作：
DESC USER_TRIGGERS;
SELECT * FROM user_triggers WHERE trigger_name = 'trigger_name';
DROP TRIGGER trigger_name;
 
触发器死循环：
1) The INSERT Trigger, triggerA on table 'abc' issues an UPDATE on table 'xyz'.
2) The UPDATE Trigger, triggerB on table 'xyz' issues an INSERT on table 'abc'.
 
以下内容摘自《Oracle PL/SQL by Example》

自治事务：

自治事务是由其他事务（通常被称为主事务）发起的独立事务，所谓独立，即主事务是否提交回滚对自治事务没有影响

例：

CREATE OR REPLACE TRIGGER instructor_aud
AFTER UPDATE OR DELETE ON INSTRUCTOR
DECLARE
v_type VARCHAR2(10);
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
IF UPDATING THEN
v_type := 'UPDATE';
ELSIF DELETING THEN
v_type := 'DELETE';
END IF;
UPDATE statistics
SET transaction_user = USER,
transaction_date = SYSDATE
WHERE table_name = 'INSTRUCTOR'
AND transaction_name = v_type;
IF SQL%NOTFOUND THEN
INSERT INTO statistics
VALUES ('INSTRUCTOR', v_type, USER, SYSDATE);
END IF;
COMMIT;
END;

 
测试：
UPDATE instructor
SET phone = '7181234567'
WHERE instructor_id = 101;
1 row updated. ROLLBACK;
SELECT *
FROM statistics;

TABLE_NAME TRANSACTIO TRANSACTION_USER TRANSACTI
----------- ---------- ---------------- ---------
INSTRUCTOR UPDATE STUDENT 09-MAR-08
 
INSTEAD OF触发器 （替换触发器）：

INSTEAD OF 触发器，是作为行触发器创建的。INSTEAD OF 触发器会代替
基于视图而执行的触发语句（INSERT/UPDATE/DELETE），并且会直接修改
底层的数据库表。
 
如果一个视图里有如下操作或者结构，视图将不能直接被
UPDATE/INSERT/DELETE语句修改。
. 集合操作：如 UNION, UNION ALL, INTERSECT, and MINUS
. 分组函数：如 AVG, COUNT, MAX, MIN, and SUM
. GROUP BY 或 HAVING 语句
. CONNECT BY 或 START WITH 语句
. DISTINCT 操作符
. ROWNUM 伪列

例子：
CREATE VIEW instructor_summary_view AS
SELECT i.instructor_id, COUNT(s.section_id) total_courses
FROM instructor i
LEFT OUTER JOIN section s
ON (i.instructor_id = s.instructor_id)
GROUP BY i.instructor_id;

结果：
DELETE FROM instructor_summary_view
WHERE instructor_id = 109;
causes the error shown:
DELETE FROM instructor_summary_view
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view

使用INSTEAD OF 触发器：
CREATE OR REPLACE TRIGGER instructor_summary_del
INSTEAD OF DELETE ON instructor_summary_view
FOR EACH ROW
BEGIN
DELETE FROM instructor
WHERE instructor_id = :OLD.INSTRUCTOR_ID;
END;

结果：
DELETE FROM instructor_summary_view
WHERE instructor_id = 109;
1 row deleted.

注意：
当操作涉及外键时，使用INSTEAD OF触发器也有可能出错。
 
 
 
标签：

相关文章