在 Oracle 中,事件或语句都能触发触发器,这也正是触发器的用武之地,具体来说有以下 4 类可以触发触发器的语句或事件:
触发器通常用来执行复杂的业务规则,用于实现由数据库完整性约束难以实现的约束,或用来自动生成分配列值、维护表数据同步、在分布式数据库中的节点之间强制引用完整性、防止无效操作、提供审计、监控对数据库的各种操作、提供透明的事件记录、当对视图发出 DML 语句时修改表数据等。
在项目中应尽可能少的使用触发器,因为过多使用触发器可能会导致复杂的相互依赖关系,这在很大的应用程序中是难以维护的。例如,当某个触发器被触发后,该触发器中的 SQL 语句又触发了其它的触发器,也就是级联触发,这可能会导致一些无法预见的影响。
尽管可以同时使用触发器和完整性约束来定义实施任何类型的完整性规则。但 Oracle 强烈建议仅在以下两种情况下使用触发器来约束数据输入:
在 Oracle 的官方手册中将触发器分为 4 类,分别是:行级触发器和语句级触发器、BEFORE 触发器和 AFTER 触发器、INSTEAD OF 触发器、事件触发器。另外,行级触发器和语句级触发器与 BEFORE 触发器和 AFTER 触发器还可以两两组合形成 4 种复合型的 DML 触发器。
1、行级触发器和语句级触发器
定义触发器时可以指定触发条件和触发频次。触发器条件是指由 WHEN 子句指定的一个逻辑表达式,当该表达式结果为 TRUE 时才会执行触发器。行级触发器的运行次数与受影响的行数相关,而语句级触发器在被触发后只运行一次。
行级触发器:触发语句每影响一行触发一次行级触发器。例如,UPDATE 表中多个行,则会对 UPDATE 语句影响的每一行触发一次行级触发器。如果触发语句不影响行,则不会运行行级触发器。如果触发操作中的代码取决于触发语句或需要访问受影响行的数据,则行级触发器很有用。
语句级触发器:无论触发语句会影响表中多少行,即使没有行受到影响,语句级触发器也会被触发一次。例如,DELETE 表中多个行,则会对 DELETE 语句影响的表触发一次语句级触发器。如果触发器操作中的代码不依赖于触发语句和受影响的行的数据,则语句级触发器很有用。
2、BEFORE 触发器和 AFTER 触发器
定义触发器时可以指定触发时机,具体来说就是可以指定是在触发事件之前还是之后执行。DML 语句触发的 BEFORE 触发器和 AFTER 触发器只能在表上定义,不能在视图上定义。如果用户执行了针对视图的 DML 语句,则会触发视图基表上的触发器。DDL 语句触发的 BEFORE 触发器和 AFTER 触发器只能在数据库或模式上定义,而不能在特定的表上定义。
BEFORE 触发器:在触发语句运行之前运行触发器操作。当需要根据情况确定是否允许触发语句执行或需要在完成触发操作之前导出特定的数据时,BEFORE 触发器很有用。
AFTER 触发器:在触发语句运行之后运行触发器操作。AFTER 触发器主要用于执行那些必须在触发操作之后才能执行的操作。
3、INSTEAD OF 触发器
INSTEAD OF 触发器提供了一种透明的方式来修改不能通过 DML 语句直接修改的视图。这些触发器也被称为替代触发器,与其它类型触发器不同的是,替代触发器由 Oracle 触发而不是执行触发语句。可以针对视图编写正常的 DML 语句,并触发 INSTEAD OF 触发器适当的更新基表中的数据。
4、事件触发器
事件触发器可以被系统事件或用户事件触发。可以触发事件触发器的事件有:数据库的启动和关机、服务器错误消息事件、用户登录和注销、执行 DDL 语句、执行 DML 语句等。
DML 触发器的类型比较多,当单个 SQL 语句触发了多个触发器时,Oracle 会按照既定的顺序依次运行每种类型的触发器。具体执行顺序如下:
Oracle 针对不同类型的触发器在设定的时间点执行完整性约束检查,并保证触发器不会危及完整性约束。如果给定的语句触发了多个相同类型的触发器,Oracle 将以未指定的随机顺序触发多个触发器;也就是说,相同语句的相同类型的触发器不能保证以任何特定的顺序触发。所以不应该重复定义触发器。
可以在数据库级别或模式级别定义系统事件触发器。DDL 语句或登录 / 注销事件触发器也可以在数据库级别或模式级别进行定义。可以在表或视图上定义 DML 语句的触发器。在数据库级别定义的触发器对所有用户公开,并且只有当触发事件涉及该模式或表时,才会触发在模式或表级别定义的触发器。
在触发器中,允许访问触发事件的某些属性。例如,数据库启动和关闭触发器具有的实例编号和数据库名称,登录和注销触发器具有的用户名,DML 语句上的触发器具有的 OLD 列值和 NEW 列值等。
另外,在创建触发器时还需要注意以下事项:
语法:
- CREATE [OR REPLACE] TRIGGER trigger_name
- {BEFORE | AFTER} -- 指定触发时机
- {INSERT | DELETE | UPDATE [OF column1 [,column2 ...]]} -- 指定触发事件
- ON [SCHEMA.]table_name -- 指定触发对象
- [REFERENCES {OLD [AS] old_name | NEW [AS] new_name | PARENT [AS] parent_name}]
- [FOR EACH ROW] -- 指定为行级触发器,不指定默认为语句级触发器
- [WHEN condition] -- 指定触发条件,只有行级触发器才能指定触发条件
- DECLARE
- -- 定义变量
- BEGIN
- -- 触发器操作代码
- EXCEPTION
- WHEN ...
- -- 异常处理代码
- END [trigger_name];
BEFORE INSERT 触发器
示例:
- CREATE OR REPLACE TRIGGER trg_bi
- BEFORE INSERT ON demo.t_course
- FOR EACH ROW
- DECLARE
- v_today DATE;
- BEGIN
- v_today := SYSDATE;
- :NEW.course_desc := '创建于 '||TO_CHAR(SYSDATE,'yyyy-mm-dd')||'。'||:NEW.course_desc;
- END;
测试:
- INSERT INTO demo.t_course VALUES(6,'计算机英语','计算机专业英语课程');
BEFORE UPDATE 触发器
示例:
- CREATE OR REPLACE TRIGGER trg_bu
- BEFORE UPDATE ON demo.t_course
- FOR EACH ROW
- DECLARE
- v_today DATE;
- BEGIN
- v_today := SYSDATE;
- IF :NEW.course_name != :OLD.course_name THEN
- :NEW.course_desc := '修改于 '||TO_CHAR(SYSDATE,'yyyy-mm-dd')||'。'||:NEW.course_desc;
- END IF;
- END;
测试:
- UPDATE demo.t_course t SET t.course_name='计算机英语(下册)' WHERE t.course_id=6;
BEFORE DELETE 触发器
示例:
- CREATE OR REPLACE TRIGGER trg_bd
- BEFORE DELETE ON demo.t_course
- FOR EACH ROW -- FOR EACH ROW 必须要在 WHEN 字句之前,否则无法创建触发器
- WHEN (OLD.course_id > 0) -- 此处 OLD 前面不能带冒号,与 PL/SQL 块中额写法相反
- DECLARE
- v_today DATE;
- v_username VARCHAR2(20);
- BEGIN
- v_today := SYSDATE;
- SELECT USER INTO v_username FROM DUAL;
- -- 将正式的课程数据备份到备份表
- INSERT INTO t_course_backup(course_id, course_name, course_desc, delete_date, deleted_by)
- VALUES(:OLD.course_id, :OLD.course_name, :OLD.course_desc, v_today, v_username);
- END;
测试:
- DELETE FROM demo.t_course t WHERE t.course_id=6;
AFTER INSERT & UPDATE & DELETE 触发器
示例:
- CREATE OR REPLACE TRIGGER trg_aiud
- AFTER INSERT OR UPDATE OR DELETE ON demo.t_course
- FOR EACH ROW
- DECLARE
- v_today DATE;
- v_username VARCHAR2(20);
- BEGIN
- v_today := SYSDATE;
- SELECT USER INTO v_username FROM DUAL;
- -- 根据不同触发语句,执行不同备份操作
- IF INSERTING THEN -- 当添加事件被触发时
- INSERT INTO t_course_backup(course_id, course_name, course_desc, insert_date, inserted_by)
- VALUES(:NEW.course_id, :NEW.course_name, :NEW.course_desc, v_today, v_username);
- ELSIF UPDATING THEN -- 当修改事件被触发时
- INSERT INTO t_course_backup(course_id, course_name, course_desc, update_date, updated_by)
- VALUES(:OLD.course_id, :OLD.course_name, :OLD.course_desc, v_today, v_username);
- ELSIF DELETING THEN -- 当删除事件被触发时
- INSERT INTO t_course_backup(course_id, course_name, course_desc, delete_date, deleted_by)
- VALUES(:OLD.course_id, :OLD.course_name, :OLD.course_desc, v_today, v_username);
- END IF;
- END;
测试:
- INSERT INTO demo.t_course VALUES(8,'微积分','高等数学');
- UPDATE demo.t_course t SET t.course_name='微积分入门' WHERE t.course_id=8;
- DELETE FROM demo.t_course t WHERE t.course_id=8;
示例:
- CREATE OR REPLACE TRIGGER trg_ddl_oper
- /**************************************************
- 功能:监控数据库的 DDL 操作,阻止所有针对 demo 的 DLL 操作(此处仅为示例,无实际意义)
- 修订记录:
- 版本号 修订时间 修订人 描述
- 1.0.0 2017-05-12 hanzz 1.创建此触发器
- **************************************************/
- BEFORE DDL ON demo.SCHEMA -- 还可以指定为某一种 DDL 或 DATABASE
- DECLARE
- event VARCHAR2(30);
- PRAGMA AUTONOMOUS_TRANSACTION;
- BEGIN
- event := ORA_SYSEVENT; -- 接收触发事件
- INSERT INTO demo.t_ddl_log(trg_event, obj_owner, obj_name, user_name, attempt_time)
- SELECT event,ORA_DICT_OBJ_OWNER,ORA_DICT_OBJ_NAME,USER,SYSTIMESTAMP FROM DUAL;
- COMMIT;
- IF event = 'CREATE' THEN
- RAISE_APPLICATION_ERROR(-20001,'禁止创建对象!');
- ELSIF event = 'DROP' THEN
- RAISE_APPLICATION_ERROR(-20002,'禁止删除对象!');
- ELSIF event = 'TRUNCATE' THEN
- RAISE_APPLICATION_ERROR(-20003,'禁止清空表!');
- ELSIF event = 'COMMENT' THEN
- RAISE_APPLICATION_ERROR(-20004,'禁止修改注释!');
- ELSE
- RAISE_APPLICATION_ERROR(-20001,'禁止 '||ORA_SYSEVENT||' 操作!');
- END IF;
- END;
1、禁止通过 PL/SQL Developer 来登录 SCOTT 用户
- CREATE OR REPLACE TRIGGER trg_deny_plsqldev
- AFTER LOGON ON DATABASE
- DECLARE
- v_program VARCHAR2(50);
- v_username VARCHAR2(50);
- BEGIN
- SELECT t.program,t.username INTO v_program,v_username FROM V$SESSION t
- WHERE t.audsid=SYS_CONTEXT('USERENV','SESSIONID') AND ROWNUM=1;
- IF LOWER(v_program)='plsqldev.exe' AND v_username='SCOTT' THEN
- RAISE_APPLICATION_ERROR(-20000,'It is forbidden to login SCOTT via PL/SQL Developer!');
- END IF;
- END;
2、限制指定用户和指定 IP 登录
- CREATE OR REPLACE TRIGGER trg_deny_login
- AFTER LOGON ON DATABASE
- DECLARE
- v_ip VARCHAR2(20);
- v_message VARCHAR2(500);
- v_deny_user_exception EXCEPTION;
- v_deny_ip_exception EXCEPTION;
- BEGIN
- v_ip := SYS_CONTEXT('USERENV','IP_ADDRESS');
- INSERT INTO demo.t_login_his(user_name, login_time, login_ip) VALUES(USER,SYSTIMESTAMP,v_ip);
- COMMIT;
- IF USER IN('SCOTT','DEMO') THEN
- RAISE v_deny_user_exception;
- END IF;
- IF v_ip = '127.0.0.1' THEN
- RAISE v_deny_ip_exception;
- END IF;
- EXCEPTION
- WHEN v_deny_user_exception THEN
- v_message := '禁止使用 SCOTT 等用户登录数据库!';
- RAISE_APPLICATION_ERROR(-20001,v_message);
- WHEN v_deny_ip_exception THEN
- v_message := '禁止在 127.0.0.1 上登录数据库!';
- RAISE_APPLICATION_ERROR(-20001,v_message);
- END;
上面两个限制登录的触发器设计思路都是:捕获到拒绝登录的用户、IP 或应用程序就抛出应用异常强制用户退出登录。因此它们的缺陷也是一样的,首先,如果用户具有 DBA 的权限,触发器就不起作用了;其次,如果用户通过 SQLPlus 登录,
就会返回空。
- SYS_CONTEXT('USERENV','IP_ADDRESS')
在 PL/SQL Developer 中,调试存储过程的功能还蛮好用的,直接在存储过程名称上右键,然后点测试即可进入调试窗口,接下来就可以调试了。而触发器似乎是不能调试的,因为触发器的右键菜单中没有测试选项,其实不然!根本原因在于触发器和存储过程的调用方式不同,用户是无法直接调用触发器的,只能由 Oracle 系统来调用,但用户可以模拟触发的动作,让 Oracle 系统自动去调用。
下面就拿
来举例说明该如何调试触发器:
- trg_aiud
或者点击开始调试器,即可进入调试模式。接下来你可以按
- F9
单步进入或
- Ctrl+N
单步跳过,需要的话也可以
- Ctrl+O
运行到底。当然也可以点调试窗口上面的那几个菜单按钮,事实上我一般都是点按钮(因为 PL/SQL Developer 的快捷键实在太不好用了)。
- Ctrl+R
- ALTER TRIGGER trg_aiud DISABLE; -- 禁用名为 trg_aiud 的触发器
- ALTER TRIGGER trg_aiud ENABLE; -- 启用名为 trg_aiud 的触发器
- ALTER TABLE t_course DISABLE ALL TRIGGERS; -- 禁用 t_course 表上所有的触发器
- ALTER TABLE t_course ENABLE ALL TRIGGERS; -- 启用 t_course 表上所有的触发器
本文从理论和实战两个方面讲述了 Oracle 触发器的方方面面,唯独没有涉及替代触发器,只因前文《.Net 程序员学用 Oracle 系列 (23):视图理论、物化视图:可更新的连接视图》中已经讲过。最后,我再重申一遍,除非是某些特殊情况,否则尽可能不用触发器,因为用多了可能会导致项目难以维护。
来源: http://www.cnblogs.com/hanzongze/p/Oracle-Trigger.html