触发器是指存放在数据库中,并且被隐含执行的存储过程。
当发生特定事件时,Oracle会自动执行触发器的相应代码。
b、触发器的类型
DML触发器;DDL触发器;替代(instead of)触发器;系统触发器。
c、触发器的组成
1、触发事件:即在何种情况下触发TRIGGER。
2、触发时间:即该TRIGGER是在触发事件发生之前(BEFORE)还是之后(AFTER)触发。
3、触发器本身:即该TRIGGER被触发之后的目的和意图,正是触发器本省要做的事情。
4、触发频率:说明触发器内定义的动作被执行的次数。
d、创建第一个触发器
示例:每次执行删除操作之后,都会信息提示:“这是删除操作!”
SQL> --当对emp01表执行删除操作之后,它就会出现一个提示信息,提示:这是删除操作
SQL> CREATE TRIGGER first_trigger
2 AFTER DELETE
3 ON emp01
4 BEGIN
5 DBMS_OUTPUT.put_line('这是删除操作!');
6 END;
7 /
Trigger created
SQL> SET SERVEROUTPUT ON
SQL> DELETE FROM emp01 WHERE empno = 7782;
这是删除操作!
1 row deleted
2、DDL触发器
a、什么是DDL触发器?
当创建、修改或者删除数据库对象时,也会引起相应的触发器操作事件,而此时就可以利用触发器来对这些数据库对象的DDL操作进行监控。
b、创建DDL触发器的语法格式
CREATE [OR REPLACE] TRIGGER 触发器的名称
[BEFORE | AFTER | INTEAD OF ] [DDL事件] ON [DATABASE | SCHEMA]
[WHEN 触发条件]
[DECLARE]
[程序的声明部分;]
BEGIN
程序的代码部分
END;
/
c、DDL事件描述和触发时机
d、代码示例1:禁止scott用户的触发器操作
禁止scott用户的DDL操作
SQL> CREATE OR REPLACE TRIGGER scott_trigger
2 BEFORE DDL
3 ON SCHEMA
4 BEGIN
5 RAISE_APPLICATION_ERROR(-20005, 'scott用户禁止所有的DDL操作');
6 END;
7 /
Trigger created
SQL> CREATE SEQUENCE test_seq;
CREATE SEQUENCE test_seq
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-20005: scott用户禁止所有的DDL操作
ORA-06512: 在 line 2
注意:RAISE_APPLICAITON_ERROR是用来测试的异常处理,能够将应用程序专有的错误从服务器端转达到客户端应用程序中(其他机器上的SQLPlus或者其他前台开发语言),其存储过程有两个参数,如:
RAISE_APPLICAITON_ERROR(error_number_in IN NUMBER, error_msg_in IN VARCHAR2);
-- error_msg_in的长度不能超过2k,超过2k后会进行截取
e、代码示例2:实现对数据库对象操作的日志记录
分解成三个步骤:
1、创建数据库对象DDL操作日志记录表;2、创建实现对数据库对象DDL操作记录的触发器;3、测试。
SQL> connect system/02000059 as sysdba;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as system AS SYSDBA
SQL> CREATE TABLE object_log( --创建数据库对象DDL操作日志记录表
2 logid number constraint pk_logid primary key,
3 operatedate date not null,
4 objecttype varchar2(50) not null,
5 objectowner varchar2(50) not null);
Table created
SQL> CREATE SEQUENCE object_log_seq;
Sequence created
SQL> CREATE OR REPLACE TRIGGER ojbect_trigger --创建实现对数据库对象DDL操作记录的触发器
2 AFTER CREATE OR DROP OR ALTER
3 ON DATABASE
4 BEGIN
5 INSERT INTO object_log(logid, operatedate, objecttype, objectowner) VALUES (object_log_seq.nextval, sysdate, ora_dict_obj_type, ora_dict_obj_owner);
6 END;
7 /
Trigger created
SQL> CREATE SEQUENCE test_seq; --测试
Sequence created
SQL> select * from object_log;
LOGID OPERATEDATE OBJECTTYPE OBJECTOWNER
---------- ----------- -------------------------------------------------- --------------------------------------------------
1 2017/12/21 SEQUENCE SYS
注意:此DDL触发器事件示例中的使用了两个属性函数(之前未使用):
ORA_DICT_OBJ_OWNER:触发DDL的数据库对象的用户;ORA_DICT_OBJ_TYPE:触发DDL的数据库对象的类型。
3、DML触发器
a、什么是DML触发器?
DML触发器是指基于DML操作所建立的触发器。
b、DML触发器的作用
DML触发器可用于实现数据安全保护、数据审计、数据完整性、参照完整性、数据复制等功能。
c、DML触发器类型
包括语句触发器和行触发器。
1、语句触发器:在指定操作语句之前或者之后执行一次,不管这条语句影响了多少行。(针对行)
2、行触发器:触发语句作用的每一条记录都被触发,在行级触发器中使用:old和:new伪记录变量,识别值的状态。(针对表)
:old表示操作该行之前,这一行的值;:new 表示操作该行之后,这一行的值。
d、创建DML触发器的语法格式
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER}
{DELETE | INSERT | UPDATE [ OF 列名]}
ON 表名
[FOR EACH ROW [WHEN (条件)])
PLSQL块
e、示例1:实现数据安全保护(数据的安全性检查)
示例:禁止在休息日(周六、周日)改变emp表的数据。
分析:1、使用to_char(sysdate, 'day')函数;2、采用语句触发器。
SQL> CREATE OR REPLACE TRIGGER emp_trigger
2 BEFORE INSERT OR UPDATE OR DELETE
3 ON emp
4 BEGIN
5 IF to_char(sysdate, 'day') IN ('星期六', '星期日') THEN
6 RAISE_APPLICATION_ERROR(-20006, '不能在休息日改变员工信息!');
7 END IF;
8 END;
9 /
Trigger created
SQL> DELETE FROM emp WHERE empno = 7369;
DELETE FROM emp WHERE empno = 7369
ORA-20006: 不能在休息日改变员工信息!
ORA-06512: 在 "SCOTT.EMP_TRIGGER", line 3
ORA-04088: 触发器 'SCOTT.EMP_TRIGGER' 执行过程中出错
f、示例2:实现数据审计
示例:审计员工信息表数据的变化,审计删除时间,以及被删除的雇员名。
使用SQL WINDOW窗口:(逐步执行)
--创建审计表
CREATE TABLE delete_emp_audit(
name VARCHAR2(10),
delete_time DATE
);
--创建触发器
CREATE OR REPLACE TRIGGER del_emp_trigger
AFTER DELETE ON emp
FOR EACH ROW
BEGIN
INSERT INTO delete_emp_audit VALUES(:old.ename, SYSDATE); --插入被删除员工的姓名和当前时间
END;
--测试
DELETE FROM emp WHERE empno = 7499;
SELECT * FROM delete_emp_audit;
在SQL语句和PLSQL语句中,old和new伪记录变量需要加上冒号:;而在WEHN这样的限制性条件语句当中,则不需要加上冒号:。
g、示例3:实现数据完整性(数据确认)
数据完整性用于确保数据满足商业逻辑或者企业规则。
实现数据完整性首选约束,约束无法实现的,可以使用触发器实现数据完整性。
示例:比如要求员工涨后工资不能低于原来的工资,并且所涨的工资不能超过原工资的50%。
SQL> CREATE OR REPLACE TRIGGER tr_check_sal
2 BEFORE UPDATE OF sal ON emp
3 FOR EACH ROW
4 WHEN (new.sal < old.sal OR new.sal > old.sal * 1.5)
5 BEGIN
6 RAISE_APPLICATION_ERROR(-20028, '工资直升不降,并且升幅不能超过50%');
7 END;
8 /
Trigger created
SQL> UPDATE emp SET sal = sal * 1.8 WHERE empno = 7902;
UPDATE emp SET sal = sal * 1.8 WHERE empno = 7902
ORA-20028: 工资直升不降,并且升幅不能超过50%
ORA-06512: 在 "SCOTT.TR_CHECK_SAL", line 2
ORA-04088: 触发器 'SCOTT.TR_CHECK_SAL' 执行过程中出错
h、示例4:实现参照完整性(比如级联更新)
为了实现级联删除,可以在定义外部键约束时指定ON DELETE CASCADE关键字。
但是使用约束却不能实现级联更新,为了实现级联更新,需要使用触发器。
示例:级联更新DEPT表的主键以及EMP表的外部键列。
CREATE OR REPLACE TRIGGER upd_cascade_trigger
AFTER UPDATE OF deptno
ON dept
FOR EACH ROW
BEGIN
UPDATE emp SET deptno = :new.deptno WHERE deptno = :old.deptno;
END;
--测试
UPDATE dept SET deptno = 50 WHERE deptno = 10;
SELECT deptno , ename FROM emp WHERE deptno = 50;
4、INSTEAD OF触发器
a、什么是INSTEAD OF触发器(替代触发器)?
适用于视图上的触发器
b、为什么使用INSTEAD OF触发器?
在简单视图上往往可以执行INSET、UPDATE、DELETE操作。
但是在复杂视图上执行INSET、UPDATE、DELETE操作时有限制。所以为了在这些复杂的视图上执行DML操作,需要建立替代触发器。
c、INSTEAD OF 触发器的限制
替代触发器只适用于视图。
替代触发器不能指定BEFORE和AFTER选项。
不能在具有WITH CHECK OPTION选项的视图上建立替代触发器。
替代触发器必须包含FOR EACH ROW选项。
d、示例代码
1、创建emp_dept视图:
SQL> CREATE OR REPLACE VIEW emp_dept
2 AS
3 SELECT d.deptno, d.dname, e.empno, e.ename
4 FROM dept d, emp e
5 WHERE d.deptno = e.deptno;
View created
2、当没有创建替代触发器时,对emp_dept视图插入数据出错:
SQL> INSERT INTO emp_dept VALUES(50, 'DEVELOPMENT', 2222, 'ALICE');
INSERT INTO emp_dept VALUES(50, 'DEVELOPMENT', 2222, 'ALICE')
ORA-01779: 无法修改与非键值保存表对应的列
3、创建emp_dept视图(复杂视图)的替代触发器:
CREATE OR REPLACE TRIGGER instead_of_trigger
INSTEAD OF
INSERT
ON emp_dept
FOR EACH ROW
DECLARE
v_temp INT;
BEGIN
SELECT COUNT(*) INTO v_temp FROM dept WHERE deptno = :new.deptno;
IF v_temp = 0 THEN
INSERT INTO dept(deptno, dname) VALUES(:new.deptno, :new.dname);
END IF;
SELECT COUNT(*) INTO v_temp FROM emp WHERE empno = :new.empno;
IF v_temp = 0 THEN
INSERT INTO emp(empno, ename, deptno) VALUES(:new.empno, :new.ename, :new.deptno);
END IF;
END;
4、对emp_dept视图进行插入操作:
SQL> INSERT INTO emp_dept VALUES(50, 'DEVELOPMENT', 2222, 'ALICE');
1 row inserted
SQL> SELECT * FROM EMP WHERE empno = 2222;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
2222 ALICE 50
SQL> SELECT *FROM DEPT WHERE deptno = 50;
DEPTNO DNAME LOC
------ -------------- -------------
50 ACCOUNTING NEW YORK
SQL> SELECT * FROM emp_dept WHERE empno = 2222;
DEPTNO DNAME EMPNO ENAME
------ -------------- ----- ----------
50 ACCOUNTING 2222 ALICE
5、系统触发器
a、什么是系统触发器?
系统触发器是由特定系统事件所触发的触发器。(需要注意的是,系统触发器是要有系统用户来创建的)
系统事件是指与例程或者方案相关的数据库事件,它包括STARTUP、SHUTDOWN、DB_ROLE_CHANGE和SERVERERROR的四种事件。
1、STARTUP事件触发器是在启动数据库后触发。
2、SHUTDOWN事件触发器在关闭数据之前触发。
3、DB_ROLE_CHANGE事件触发器在改变角色后第一次打开数据库时触发。
4、SERVERERROR事件触发器在发生Oracle错误时触发。
b、示例代码
1、创建记录发生的数据库系统EVENT_TALBE事件表:
CREATE TABLE event_table(
event VARCHAR2(50),
event_time date
);
2、创建系统触发器:
SQL> CREATE OR REPLACE TRIGGER startup_trigger
2 AFTER STARTUP ON DATABASE
3 BEGIN
4 INSERT INTO event_table VALUES(ORA_SYSEVENT, SYSDATE);
5 END;
6 /
Trigger created