1.什么是触发器
- 触发器是一种特殊的存储过程
- 每当一个特定的数据操作语句(insert,update,delete)在指定的表上发出时,Oracle自动地执行触发器中定义的语句序列。
2.触发器的应用场景
- 复杂的安全性检查
- 数据确认
- 实现审计功能
- 完成数据的备份和同步
3.最简单的触发器
3.0 触发器基本语法
create or replace trigger 触发器名
before | after
delete | insert | update [of 列名]
on 表名
[for each row [when 条件]] -- 行级触发器 或 语句级触发器
plsql程序块
3.1 语句级触发器
在指定的操作语句操作之前或之后执行一次,不管这条语句影响了多少行 -- 针对的是表
3.2行级触发器
触发语句作用的每一条记录都被触发。在行级触发器中使用 :old 和 :new 伪记录变量,识别值的状态。-- 针对的是行
create or replace TRIGGER say_hi_to_new_user
after insert
on tb_user
declare
begin
dbms_output.put_line('Hello New User...');
end;
/
insert into tb_user(no,name,salary,job,hiredate,deptno) values(10,'NikoBelic',5000,'MANAGER',sysdate,20);
输出
1行已插入。
Hello New User...
4.触发器应用实例
4.1 案例一:复杂安全性检查 禁止在非工作时间插入员工
create or replace trigger time_check
before insert
on tb_user
declare
begin
if to_char(sysdate,'day') in ('星期六','星期日') or
to_number(to_char(sysdate,'hh24')) not between 9 and 17 then
RAISE_APPLICATION_ERROR(-20001, '禁止在非工作时间插入新员工');
end if;
end;
/
insert into tb_user(no,name,salary,job,hiredate,deptno) values(11,'NikoBelic',5000,'MANAGER',sysdate,20);
输出:
错误报告 -
SQL 错误: ORA-20001: 禁止在非工作时间插入新员工
ORA-06512: 在 "LEARN.TIME_CHECK", line 7
ORA-04088: 触发器 'LEARN.TIME_CHECK' 执行过程中出错
4.2 案例二:数据的确认
/*
触发器案例2:数据的确认
涨后工资不能少于涨前工资
1. :old ,:new 代表同一条记录
2. :old 表示操作该行之前,这一行的值
:new 表示操作改行之后,这一行的值
*/
create or replace trigger check_salary
before update
on tb_user
for each row
begin
-- if 涨后的工资 < 涨前的工资 then
if :new.salary < :old.salary then
RAISE_APPLICATION_ERROR(-20002, '涨后工资比涨前工资还低?滚!');
end if;
end;
/
update tb_user t set t.salary = t.salary - 100 where t.no = 1;
输出:
错误报告 -
SQL 错误: ORA-20002: 涨后工资比涨前工资还低?滚!
ORA-06512: 在 "LEARN.CHECK_SALARY", line 3
ORA-04088: 触发器 'LEARN.CHECK_SALARY' 执行过程中出错
4.3案例三:数据库的审计
/*
触发器应用场景三:数据库的审计 -->基于值的审计功能
给员工涨工资,当涨后薪资超过6000时,审计该员工信息
*/
create table tb_audit(
infomation varchar2(200)
);
create or replace trigger audit_user
after update
on tb_user
for each row
begin
-- 当涨后工资超过6000时,插入审计信息
if :new.salary > 6000 then
insert into tb_audit(infomation) values(:new.name || ' ' || :new.salary);
end if;
end;
update tb_user t set t.salary = t.salary + 2000;
4.4 案例四:数据的备份和同步
当给员工涨完工资后,自动备份新的工资到备份表中
创建备份表
create table tb_user_back as select * from tb_user;
create or replace trigger sync_salary
after update
on tb_user
for each row
begin
update tb_user_back set salary = :new.salary where no = :new.no;
end;
/
update tb_user t set t.salary = t.salary + 1 where t.no = 1 ;
4.5 案例5, 防止数据重复插入
此案例结合存储过程锁,可以有效防止高并发问题。
create or replace trigger check_repeat
before insert
on tb_user
for each row
declare
cursor cur_user is select name from tb_user;
pname tb_user.name%type;
begin
open cur_user;
loop
fetch cur_user into pname;
exit when cur_user%notfound;
dbms_output.put_line(pname);
if :new.name = pname then
RAISE_APPLICATION_ERROR(-20003, '该用户已经被存储过了');
end if;
end loop;
end;
/
insert into tb_user(no,name,salary,job,hiredate,deptno) values(12,'Zhangyu',5000,'MANAGER',sysdate,20);
输出
在行: 23 上开始执行命令时出错 -
insert into tb_user(no,name,salary,job,hiredate,deptno) values(12,'Zhangyu',5000,'MANAGER',sysdate,20)
错误报告 -
SQL 错误: ORA-20003: 该用户已经被存储过了
ORA-06512: 在 "LEARN.CHECK_REPEAT", line 12
ORA-04088: 触发器 'LEARN.CHECK_REPEAT' 执行过程中出错