Oracle数据库之PLSQL触发器
Oracle数据库之PL/SQL触发器
1. 介绍
触发器（trigger）是提供给程序员和数据分析员来保证数据完整性的⼀种⽅法，它是与表事件相关的特殊的存储过程，它的执⾏不是由程序调⽤，也不是⼿⼯启动，⽽是由事件来触发，⽐如当对⼀个表进⾏操作（insert，delete，update）时就会激活它执⾏。触发器经常⽤于加强数据的完整性约束和业务规则等。
触发器有三种类型，分别是：DML触发器、替代触发器和系统触发器。
DML触发器
顾名思义，DML触发器是由DML语句触发的。例如数据库的INSERT、UPDATE、DELETE操作都可以触发该类型的触发器。它们可以在这些语句之前或之后触发，或者在⾏级上触发(就是说对于每个受影响的⾏都触发⼀次)。
替代触发器
替代触发器只能使⽤在视图上，与DML不同的是，DML触发器是运⾏在DML之外的，⽽替代触发器是代替激发它的DML语句运⾏。替代触发器是⾏触发器。
系统触发器
这种触发器是发⽣在如数据库启动或关闭等系统事件时，不是在执⾏DML语句时发⽣，当然也可以在DDL时触发。
触发器功能强⼤，轻松可靠地实现许多复杂的功能，但是我们也应该慎⽤。为什么⼜要慎⽤呢？触发器本⾝没有过错，但如果我们滥⽤，会造成数据库及应⽤程序的维护困难。在数据库操作中，我们可以通过关系、触发器、存储过程、应⽤程序等来实现数据操作，同时约束、缺省值也是保证数据完整性的重要保障。如果我们对触发器过分的依赖，势必影响数据库的结构，同时增加了维护的复杂程度。
2. 触发器组成
触发器主要由以下⼏个要素组成：
1. 触发事件：引起触发器被触发的事件。
2. 触发时间：触发器是在触发事件发⽣之前（BEFORE）还是之后(AFTER)触发，也就是触发事件和该触发器的操作顺序。
3. 触发操作：触发器被触发之后的⽬的和意图，是触发器本⾝要做的事情。
4. 触发对象：包括表、视图、模式、数据库。只有在这些对象上发⽣了符合触发条件的触发事件，才会执⾏触发操作。
5. 触发条件：由WHEN⼦句指定⼀个逻辑表达式。只有当该表达式的值为TRUE时，遇到触发事件才会⾃动执⾏触发器，使其执⾏触发
操作。
6. 触发频率：说明触发器内定义的动作被执⾏的频率。即语句级(STATEMENT)触发器和⾏级(ROW)触发器：
plsql developer怎么执行语句语句级(STATEMENT)触发器：是指当某触发事件发⽣时，该触发器只执⾏⼀次；
⾏级(ROW)触发器：是指当某触发事件发⽣时，对受到该操作影响的每⼀⾏数据，触发器都单独执⾏⼀次。
3. 创建触发器
语法：
CREATE [ OR REPLACE ] TRIGGER plsql_trigger_source
plsql_trigger_source ::=
[schema.] trigger_name
{ simple_dml_trigger
| instead_of_dml_trigger
| compound_dml_trigger
| system_trigger
}
simple_dml_trigger ::=
{ BEFORE | AFTER } dml_event_clause [ referencing_clause ] [ FOR EACH ROW ]
[ trigger_edition_clause ] [ trigger_ordering_clause ]
[ ENABLE | DISABLE ] [ WHEN ( condition ) ] trigger_body
instead_of_dml_trigger ::=
INSTEAD OF { DELETE | INSERT | UPDATE } [ OR { DELETE | INSERT | UPDATE } ]...
ON [ NESTED TABLE nested_table_column OF ] [ schema. ] noneditioning_view
[ referencing_clause ] [ FOR EACH ROW ]
[ trigger_edition_clause ] [ trigger_ordering_clause ]
[ ENABLE | DISABLE ] trigger_body
system_trigger ::=
{ BEFORE | AFTER | INSTEAD OF }
{ ddl_event [OR ddl_event]...
| database_event [OR database_event]...
}
ON { [schema.] SCHEMA
| DATABASE
}
[ trigger_ordering clause ]
dml_event_clause ::=
{ DELETE | INSERT | UPDATE [ OF column [, column ]... ] }
[ OR { DELETE | INSERT | UPDATE [ OF column [, column]... ] }...
ON [ schema.] { table | view }
referencing_clause ::=
REFERENCING
{ OLD [ AS ] old
| NEW [ AS ] new
| PARENT [ AS ] parent
}...
trigger_body ::=
{ plsql_block | CALL routine_clause }
说明：
BEFORE和AFTER指出触发器的触发时间分别为前触发和后触发⽅式，前触发是在执⾏触发事件之前触发当前所创建的触发器，后触发是在执⾏触发事件之后触发当前所创建的触发器。
REFERENCING⼦句说明相关名称，在⾏触发器的PL/SQL块和WHEN⼦句中可以使⽤相关名称参照当前的新、旧列值，默认的相关名称为OLD和NEW。触发器的PL/SQL块中应⽤相关名称时，必须在它们之前加冒号(:)，但在WHEN⼦句中则不能加冒号。
NEW只在UPDATE、INSERT的DML触发器内可⽤，它包含了修改发⽣后被影响⾏的值。
OLD只在UPDATE、DELETE的DML触发器内可⽤，它包含了修改发⽣前被影响⾏的值。
FOR EACH ROW选项说明触发器为⾏触发器。⾏触发器和语句触发器的区别表现在：⾏触发器要求当⼀个DML语句操⾛影响数据库中的多⾏数据时，对于其中的每个数据⾏，只要它们符合触发约束条件，均激活⼀次触发器；⽽语句触发器将整个语句操作作为触发事件，当它符合约束条件时，激活⼀次触发器。当省略FOR EACH ROW 选项时，BEFORE和AFTER触发器为语句触发器，⽽INSTEAD OF触发器则只能为⾏触发器。
WHEN⼦句说明触发约束条件。Condition为⼀个逻辑表达时，其中必须包含相关名称，⽽不能包含查询语句，也不能调⽤PL/SQL函数。WHEN⼦句指定的触发约束条件只能⽤在BEFORE和AFTER⾏触发器中，不能⽤在INSTEAD OF⾏触发器和其它类型的触发器中。INSTEAD OF选项（创建替代触发器）使ORACLE激活触发器，⽽不执⾏触发事件。只能对视图和对象视图建⽴INSTEAD OF触发器，⽽不能对表、模式和数据库建⽴INSTEAD OF触发器。
ddl_event：⼀个或多个DDL事件，事件间⽤OR分开。
database_event：⼀个或多个数据库事件，事件间⽤OR分开。
⽰例1，在插⼊数据时，⾃动使⽤序列编号：
CREATE OR REPLACE TRIGGER EMP_INSERT_ID
BEFORE INSERT ON employee FOR EACH ROW
BEGIN
SELECT SEQ_ID.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
⽰例2，在多表联接的视图中插⼊数据：
-- 创建视图
CREATE OR REPLACE VIEW vw_emp AS
SELECT e.name ename, e.address, d.name dname
FROM employee e, dept d
WHERE e.did = d.id;
-- 创建触发器
CREATE TRIGGER emp_insert_trigger
INSTEAD OF INSERT ON vw_emp
DECLARE
v_did dept.id%TYPE;
BEGIN
SELECT id INTO v_did FROM dept WHERE name = :NEW.dname;
INSERT INTO emp (name, address, did) VALUES (:ame, :NEW.address, v_did);
END emp_insert_trigger;
⽰例3，创建实例启动触发器：
-
- 创建记录操作事件的表
CREATE TABLE event_table(
event VARCHAR2(50),
time DATE
);
-- 创建触发器
CREATE OR REPLACE TRIGGER tr_startup
AFTER STARTUP
ON DATABASE
BEGIN
INSERT INTO event_table(event, time)
VALUES(ora_sysevent, SYSDATE);
END;
4. DML触发器
DML触发器对我们开发⼈员来说是最常⽤的。DML触发器是由数据库的INSERT、UPDATE、DELETE操作触发，该类触发器可以在上述语句之前或之后执⾏，也可以每个受影响的⾏执⾏⼀次。
条件谓词：当在触发器中包含多个触发事件（INSERT、UPDATE、DELETE）的组合时，为了分别针对不同的事件进⾏不同的处理，需要使⽤ORACLE提供的条件谓词：
1. INSERTING：当触发事件是INSERT时，取值为TRUE，否则为FALSE。
2. UPDATING [（column_1,column_2,…,column_x）]：当触发事件是UPDATE时，如果修改了column_x列，则取值为TRUE，否则为
FALSE。
3. DELETING：当触发事件是DELETE时，则取值为TRUE，否则为FALSE。
⽰例：
CREATE OR REPLACE TRIGGER emp_sal_trigger
BEFORE UPDATE OF salary OR DELETE
ON employee FOR EACH ROW
WHEN (old.did = 1)
BEGIN
CASE
WHEN UPDATING ('salary') THEN
IF :NEW.salary < :old.salary THEN
RAISE_APPLICATION_ERROR(-20001, '部门1的员⼯⼯资不能降');
END IF;
WHEN DELETING THEN
RAISE_APPLICATION_ERROR(-20002, '不能删除部门1的员⼯记录');
END CASE;
END emp_sal_trigger;
5. 替代触发器
INSTEAD OF⽤于对视图的DML触发，由于视图有可能是由多个表联结(JOIN)⽽成，因⽽并⾮所有的视图都是可更新的，但可以按照所需的⽅式执⾏更新。
创建INSTEAD OF触发器需要注意以下⼏点：
1. 只能被创建在视图上，并且该视图没有指定WITH CHECK OPTION选项。
2. 不能指定BEFORE或AFTER选项。
3. FOR EACH ROW⼦句是可选的。
4. 没有必要在针对⼀个表的视图上创建INSTEAD OF触发器，只要创建DML触发器就可以了。
⽰例：
CREATE OR REPLACE TRIGGER emp_delete_trigger
INSTEAD OF DELETE ON vw_emp FOR EACH ROW
DECLARE
v_did dept.id%TYPE;
BEGIN
SELEC id INTO v_did FROM dept WHERE name=:OLD.dname;
DELETE FROM employee WHERE did= v_did;
END emp_delete_trigger;
6. 系统触发器
系统触发器可以在DDL或数据库系统上被触发，数据库系统事件包括数据库服务器的启动或关闭，⽤户的登录与退出、数据库服务错误等。
系统事件触发器既可以建⽴在⼀个模式上，⼜可以建⽴在整个数据库上。当建⽴在模式(SCHEMA)之上时，只有模式所指定⽤户的DDL操作和它们所导致的错误才激活触发器，默认时为当前⽤户模式。当建⽴在数据库(DATABASE)之上时，该数据库所有⽤户的DDL操作和他们所导致的错误，以及数据库的启动和关闭均可激活触发器。
系统触发器的种类和事件出现的时机：
事件触发时机说明
STARTUP AFTER启动数据库实例之后触发
SHUTDOWN BEFORE关闭数据库实例之前触发
SERVERERROR AFTER数据库服务器发⽣错误之后触发
LOGON AFTER成功登录到数据库后触发
LOGOFF BEFORE断开数据库连接之前触发
DDL BEFORE，AFTER在执⾏⼤多数DDL语句之前、之后触发
CREATE / ALTER / DROP BEFORE，AFTER在执⾏CREATE或ALTER或DROP语句创建数据库对象之前、之后触发RENAME BEFORE，AFTER执⾏RENAME语句更改数据库对象名称之前、之后触发
GRANT / REVOKE BEFORE，AFTER执⾏GRANT语句授予权限或REVOKE撤销权限之前、之后触发AUDIT / NOAUDIT BEFORE，AFTER执⾏AUDIT或NOAUDIT进⾏审计或停⽌审计之前、之后触发
⽰例：
-- 创建记录⽤户登录注销⽇志的表
CREATE TABLE log_on_off_log
(user_name VARCHAR2(20),
logon_date timestamp,
logoff_date timestamp);
-- 创建登录触发器
CREATE OR REPLACE TRIGGER logon_trigger
AFTER LOGON ON DATABASE
BEGIN
INSERT INTO log_on_off_log (user_name, logon_date) VALUES (ora_login_user, systimestamp);
END logon_trigger;
-- 创建退出触发器
CREATE OR REPLACE TRIGGER logoff_trigger
BEFORE LOGOFF ON DATABASE
BEGIN
INSERT INTO log_on_off_log (user_name, logoff_date) VALUES (ora_login_user, systimestamp);
END logoff_trigger;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。