Oracle触发器
触发器(trigger)提供给程序员和数据分析员来保证数据完整性的一种方法,它是与表事件相关的特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件来触发,比如当对一个表进行操作(insert,delete,update)时就会激活它执行,触发器经常用于加强数据的完整性约束和业务规则等。
触发器是附加在数据库对象上的一段代码,它指定了触发的时机和事件,如:before/after/instead of和insert,update,delete。一般在操作这些有附加触发器对象时,用触发器记录操作日志,或进行数据备份和数据校验。
触发器会给数据库带来不稳定,因为它可能在执行时抛出异常而影响正常的数据操作,有时数据库对象上附加的触发器个数多,或流程复杂,势必会影响数据库性能,所以,一般不建议使用,除非特别清楚触发器的风险和隐患。想达到某一种目的,往往有多个替代方案。
触发器一般建立在表或视图上,也可以建立在用户或数据库层面上。触发器不能主动调用,只有在对相应对象操作时自动触发。
一、 触发器的作用
- 可在写入数据表前,强制检验或转换数据。
- 触发器发生错误时,异动的结果会被撤销。
- 部分数据库管理系统可以针对数据定义语言(DDL)使用触发器,称为DDL触发器
- 可依照特定的情况,替换异动的指令(INSTEAD OF)
二、 DML触发器
DML触发器有语句级和行级
语句级
语句级触发器对一个SQL操作只做一次触发,不能访问具体的行、列数据,适合记录操作日志。
- 创建操作日志表:
create table T_LOGS
(
id NUMBER,-- 主键
op_time DATE, -- 操作时间
op_user VARCHAR2(20), -- 操作用户
op_event CHAR(6), -- 操作事件类型insert/update/delete
op_target VARCHAR2(50)-- 操作的目标对象
);
- 创建语句级触发器:
-- 关键字trigger
create or replace trigger trg_emp1
before insert or update or DELETE -- 时机和事件,事件可以是它们的组合
on emp -- 作用的对象
declare
v_use VARCHAR2(20);-- 操作用户
v_event CHAR(6);-- 操作事件
BEGIN
-- INSERTING UPDATING DELETING USER这些都是Oracle的常量,可以直接使用
IF INSERTING THEN
v_event:='增加';
ELSIF UPDATING THEN
v_event:='修改';
ELSIF DELETING THEN
v_event:='删除';
END IF;
INSERT INTO t_logs(id,op_time,op_user,op_event,op_target)
VALUES(seq_id.nextval,SYSDATE,USER,v_event,'emp');
end trg_emp1;
行级
给dept表建行级触发器,操作dept表的同时更新它的副表dept2,增删改时两张表数据同步:
CREATE OR REPLACE TRIGGER trg_dept
BEFORE INSERT OR UPDATE OR DELETE -- 时机和事件
ON dept -- 作用的对象
FOR EACH ROW -- 代表行级
DECLARE
BEGIN
-- :new代表了新加入emp表中的数据行,可以得到每一列的值
-- :old代表原来的行,在进行修改和删除时需要通过:old获取原来的数据
IF inserting THEN
INSERT INTO dept2 VALUES(:new.deptno,:new.dname,:new.loc);
ELSIF updating THEN
UPDATE dept2 SET deptno=:new.deptno,dname=:new.dname,loc=:new.loc WHERE deptno=:old.deptno;
ELSE
DELETE FROM dept2 WHERE deptno=:old.deptno;
END IF;
END;
三、 DDL触发器
在scoot用户下操作数据库对象时可以记录操作日志:
CREATE OR REPLACE TRIGGER trg_scott1
AFTER DDL ON scott.schema -- 对scott用户进行DDL的监控
BEGIN
/*ora_dict_obj_nameDDL操作的对象名
ora_dict_obj_typeDDL操作的对象类型table、view、db_name*/
INSERT INTO t_logs(ID,op_time,op_user,op_event,op_target)
VALUES(seq_id.nextval,SYSDATE,'scott',ora_sysevent
,ora_dict_obj_name||'-'||ora_dict_obj_type);
END;
注:建立了触发器后,不能再对t_logs表进行修改和删除,因为这也是DDL操作,而这个表正是记录这些操作的日志表,会报错。
四、 数据库级触发器
以DBA角色登录数据库,创建触发器,记录用户登录的日志:
-- 因为是DBA角色登录,所以代码中的数据对象要有用户schema
--scott就是scott的用户schema
CREATE OR REPLACE TRIGGER trg_logon
AFTER LOGON ON DATABASE -- 对数据库登录监控 BEFORE LOGOFF可以在用户登出时记录日志
BEGIN
-- sys_context('USERENV', 'SESSIONID')是获取用户的会话标识
INSERT INTO scott.t_logs(ID,op_time,op_user,op_event,op_target)
VALUES(scott.seq_id.nextval,SYSDATE,user,'LOGIN'
,sys_context('USERENV', 'SESSIONID'));
END;
五、 替代触发器
替代视图完成表数据的维护,instead of,before,after是三选一,所以替代触发器是没有before和after选项。
create or replace trigger trg_myvw
instead of insert or update or delete
on my_vw
for each row
declare
-- local variables here
begin
IF INSERTING THEN
INSERT INTO emp(empno) VALUES(:new.empno);
ELSIF UPDATING THEN
-- :new是新数据行
UPDATE emp SET ename=:new.ename WHERE empno=:new.empno;
ELSE
-- 对于没有更新操作来说,:old就是原数据行
-- 需要通过:old来获取数据,:new在此处不适合
DELETE emp WHERE empno=:old.empno;
END IF;
end trg_myvw;