oracle触发器测试使用

mahan

触发器是一种数据库对象。在事先为某张表绑定一段代码，当表中的数据发生增、删、改的时候，系统会自动触发代码并执行。

作用：检查输入的数据；实时备份表格的数据；记录表格操作的日志。

注：触发器发生错误时，前面用户已经执行成功的操作会被撤销，类似事务的回滚。

触发器在业务上主要应用于数据库的备份和审计。

触发器的类型分为：前置触发器、后置触发器。

区别：

触发时间：前置触发器是在执行DML之前被激活；而后置触发器是在执行DML之后被激活。所以前置触发器可以在DML之前验证或修改数据；而后置触发器在DML之后对数据进行处理，常用于日志记录和数据统计。
功能上：前置触发器可以做增、删、改的操作；而后置触发器不能，只能做select操作。
11.1 用法
创建触发器：

create or replace trigger 触发器名字

before|after insert or update or delete on 表名

for each row

begin

执行的sql语句;

end;

--前置触发器：before

--后置触发器：after

删除触发器：

drop trigger 触发器名称;

11.2 案例（前置触发器--验证数据）
需求：在emp表中如果要去更新用户的工资，新增的用户，工资不能超过2000元；老用户涨工资不能超过原来工资的10%；禁止删除岗位PRESIDENT。

create or replace trigger check_emp_sal

before insert or update or delete on emp

for each row

begin

if inserting then

if :new.sal>2000 then

raise_application_error('-20007','新员工工资不能超过2000');

end if;

elsif updating then

if :new.sal>:old.sal*1.1 then

raise_application_error('-20008','老员工涨工资不能超过原来的10%');

end if;

else

if :old.job='PRESIDENT' then

raise_application_error('-20006','不能删除岗位是PRESIDENT的员工信息');

end if;

end if;

end;

--这里报错：无法对sys拥有的对象创建触发器，换个普通用户，把emp表备份出来，再创建触发器

create table emp as select * from scott.emp;

--测试触发器

insert into emp values(6666,'ADDFD66','CLERK',null,date'2021-09-20',2001,null,40);

insert into emp values(6667,'ADDFD67','CLERK',null,date'2021-09-20',1500,null,40);

update emp set sal=4000 where empno=7369;

update emp set sal=850 where empno=7369;

delete from emp where job='CLERK' and sal<2000;

delete from emp where job='PRESIDENT';

经测试，结果符合预期，满足需求。

注：

sys的对象不能用来创建触发器，会报错。
2. "："有2中意思；一：给变量赋值， 如， names varchar2(10) :='aa'; 这是把"aa"赋值给变量names。二：表示引用，即引用表中字段所对应的值，如：emp表中有个name为“Bob”，那么 : old.name 的值就是"Bob"。只是表示引用的时候，只能出现在触发器里面。
:NEW 和:OLD使用方法和意义，new 只出现在insert和update时，old只出现在update和delete时。在insert时new表示新插入的行数据，update时new表示要替换的新数据、old表示要被更改的原来的数据行，delete时old表示要被删除的数据。
11.3 案例（后置触发器--审计）
触发器的另一个常见用途是为了之后审计的目的而记录的对数据库的修改。

比如：当一个人增加或删除了某条记录的时候，我们可以把这个操作记录下来。这样就知道了谁进行了什么样的操作。

需求：对dept表进行增删改，建立记录其改动的操作的审计表。

--1.先创建审计表。

create table dept_audit(

user_name varchar(64),

action_type varchar(64),

action_date date,

new_deptno number(2),

old_deptno number(2),

new_dname varchar(16),

old_dname varchar(16),

new_loc varchar(16),

old_loc varchar(16)

);

select *from dept_audit;

--2.创建触发器：

create or replace trigger after_dept

after insert or update or delete on dept

for each row

begin

if inserting then

--dept表插入数据时

insert into dept_audit

values(user,'insert',sysdate,:new.deptno,'',:new.dname,'',:new.loc,'');

elsif updating then

--dept表更新数据时

insert into dept_audit

values(user,'update',sysdate,:new.deptno,:old.deptno,:new.dname,:old.dname,:new.loc,:old.loc);

else

--dept表删除数据时

insert into dept_audit

values(user,'delete',sysdate,'',:old.deptno,'',:old.dname,'',:old.loc);

end if;

end;

--3.验证触发器的结果

insert into dept values(60,'java','chongqing');

update dept set dname='python' where deptno=32;

delete from dept where deptno=60;

select * from dept_audit;

结果显示：



这里如果换个用户A登陆来对YANGFENG用户下的dept表的数据进行修改，那么user_name的数据也会变成用户A。