PLSQL触发器

现有如下题目：

有表student(s_no, s_name, s_age, s_grade)，其中s_no-学号，也是主键，是从1开始向上排的
（例如：第一个学生学号是1，第二个是2，一次类推）；s_name-学生姓名；s_age-学生年龄；s_grade-年级；
这张表的数据量有几千万甚至上亿。一个学年结束了，我要让这些学生全部升一年级，即，让s_grade字段加1。
这条sql，写出来如下：
update student set s_grade=s_grade+1;
但是我们直接运行这条sql，会因数据量太大会把数据库undo表空间撑爆，从而发生异常。
那我们来写个存储过程，进行批量更新，我们每10万条提交一次。

1
2
3
4
5
6
7
8
9
10
11
12
13
create or replace procedure pro_student is
  zongrenshu number;
  xuehao number :=0;
begin
  select count(1) into zongrenshu from student;--统计学员总人数
  while xuehao < zongrenshu loop
    xuehao := xuehao + 1;
    update student set s_grade=s_grade+1 where s_no = xuehao;--每次循环更新一条数据
    if mod(xuehao,100000) = 0 then commit;---每10W条提交一次
    end if;
  end loop;
  commit;---把最后不足10W条的数据提交
end;

触发器 trigger

一、概念

触发器是一个pl/sql块，类似存储过程和函数，是很多关系型数据库都提供的功能

1、触发器与存储过程/函数的区别

  • 存储过程和函数创建之后，想要使用用户主动必须调用，触发器通过提前定义一个事件，当事件执行，自动触发
  • 存储过程和函数都可以传递参数，而触发器不可以

2、触发器的作用

监控对数据库的各种操作，从而实现审计工作

二. 触发器的组成部分

1.触发事件 通常为DML语句的增删改，对表或视图进行操作的事件
2.触发时间 before 或 after
3.触发操作 begin end 中写的内容
4.触发对象 表，视图，数据库
5.触发频率 表级与行级
表级 ：针对一张表的操作只触发一次，为默认等级
行级 ： for each row 针对于每一行数据的操作都会触发

三. 触发器的分类

  1. DML触发器： 针对表的DML操作所创建的触发器
  2. 替代触发器： 针对视图
  3. 系统触发器： 针对数据库的登入登出，启动关闭等情况

四. 语法

1.DML 触发器

create or replace trigger 触发器名
before/after —在代码运行之前触发还是之后
insert or update or delete —or 是记录多种操作时起连接作用的
on 表名
for each row –行级触发器，不写默认表级触发器
begin
触发后要执行的操作;
end;

例题：
创建一个触发器，当我们删除empa表中的数据时，打印’检测到目标被修改’

1
2
3
4
5
6
7
8
9
10
11
create or replace trigger tri_xiugai
  after delete on empa
  for each row
begin
  dbms_output.put_line('检测到目标被修改');
end;

--drop table empa;
--create table empa as select * from emp;
select * from empa;
delete from empa;

例题：
创建一个触发器，当我们删除empa表中的数据时，打印’检测到目标被删除’，
修改empa表中的数据时，打印’检测到目标被修改’，
给empa表插入数据时，打印’检测到目标被添加’，

属性：

==inserting==

==updating==

==deleting==

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
create or replace trigger tri_xiugai
  after delete or update or insert on empa
  for each row
begin
  if deleting then 
  dbms_output.put_line('检测到目标被删除');
  elsif  updating  then 
    dbms_output.put_line('检测到目标被修改');
  elsif  inserting  then 
    dbms_output.put_line('检测到目标被添加');
  end if;
end;

delete from empa where ename = 'SMITH';
update empa set deptno = 40 where ename = 'SCOTT';
insert into empa(ename) values('罗语萱');

参数：

只针对列名起作用

==:old== 旧数据 ，原数据 此参数获取一行数据

==:new== 新数据 此参数获取一行数据

例：创建一个触发器，当修改数据时，打印原数据为XXX，修改后的数据为XXX

1
2
3
4
5
6
7
create or replace trigger tri_xiugai
  after update on empa
  for each row
begin
  if 
  dbms_output.put_line('原数据为:'||:old.sal||'修改后的数据为:'||:new.sal);
end;

例：编写一个触发器实现如下功能：
对修改empa表职工薪金的操作进行合法性检查：
a) 修改后的薪金要大于修改前的薪金
b) 工资增量不能超过原工资的10%
c) 目前没有所属部门的职工不能涨工资
d) 违反以上限制抛出相应异常
create or replace trigger tr_ri
after update on empa
for each row
begin
if :new.sal < :old.sal then
raise_application_error(-20000, ‘修改后的薪金要大于修改前的薪金’);
elsif :new.sal > :old.sal * 1.1 or :old.sal is null then
raise_application_error(-20000, ‘工资增量不能超过原工资的10%’);
elsif :old.deptno is null then
raise_application_error(-20000, ‘目前没有所属部门的职工不能涨工资’);
end if;
end;

update empa set sal = nvl(sal, 0) + 1000 where ename = ‘123’;
select*from empa;

例：创建一个empa表的日志表empa_rizhi，创建一个触发器，当empa表中的数据被删除时，将这条数据保存到empa_rizhi中
create table empa_rizhi as select * from emp where 1=2;
select * from empa_rizhi;

create or replace trigger tr_x
after delete on empa
for each row
begin
insert into empa_rizhi(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno);
end;

delete from empa where ename=’123’;

例：创建一个（级联删除功能）触发器，当我们直接删除dept表中数据时，不会告诉我们违反外键约束，并且能删除成功
select * from empa;
select * from depta;

alter table depta modify(deptno number constraint pk_depta_deptno primary key);
alter table empa modify(deptno number ,constraint fk_empa_deptno foreign key(deptno) references depta(deptno));

select * from empa;
select * from depta;

delete from dept where deptno = 40;

create or replace trigger tr_shan before delete on depta for each row
begin
delete from empa where deptno=:old.deptno;
end;

delete from depta where deptno=20;

2.替代触发器 instead of

–主要用于解决视图修改问题
–不能修改虚拟字段（原表中没有的字段）
create or replace trigger 触发器名
instead of
delete or insert or update on depta
for each row
begin
触发后的操作;
end;

例题：使用替代触发器修改试图中的地址
create view v_emp_dept as
select a.empno,a.ename,a.job,a.sal,b.dname,b.loc from emp a join dept b on a.deptno=b.deptno;

select * from v_emp_dept;

update v_emp_dept set loc=’南京’ where dname=’ACCOUNTING’;

update dept set loc=’南京’ where dname=’ACCOUNTING’;

drop view v_emp_dept;

create or replace trigger tri_a
instead of
delete or insert or update on v_emp_dept
for each row
begin
update dept set loc = :new.loc where dname = :old.dname;
–获取对视图的修改语句，对源表做出改变，以此实现用直接修改视图的效果
end;

update v_emp_dept set loc=’南京’ where dname=’ACCOUNTING’;

  1. 系统触发器–拥有管理员权限
    database 数据库

使用系统触发器记录户登录登出情况
—-创建日志表，用于存放用户登录登出数据—-
create table log_event(username varchar2(10),log_type varchar2(10),logon date,logoff date);
drop table log_event;
select * from log_event;

—-创建登录触发器—-
create or replace trigger tri_logon
after logon on database–在数据库中登录之后
begin
insert into log_event(username,log_type,logon) values(ora_login_user,’登录’,sysdate);
end;

—-创建登出触发器—-
create or replace trigger tri_logoff
before logoff on database–在数据库中登出之前
begin
insert into log_event(username,log_type,logoff) values(ora_login_user,’登出’,sysdate);
end;