Oracle学习笔记11：触发器

1.什么是触发器？

触发器就是一个特殊的存储过程，当执行某些特定事件时（如DML操作，DDL操作，数据库事件），激活代码自动执行。

（个人理解，就像是前端页面的按钮一样， 给按钮添加一个点击事件，当点击按钮时自动执行JavaScript代码）

注意： 1.触发器不能包含事务控制语句，因为触发器是触发语句的一部分，触发语句被提交回退时，触发器也被提交回退了。

　　     2.触发器中执行的操作不能为DDL操作（不能在激活触发器时，create、drop、alter）

　　　 3.触发器内的代码有大小限制，最大为32KB，如果要执行的代码文件过大时，可以将代码放入存储过程中，然后通过触发器调用存储过程

　　　 4.触发器不接受参数

　　　 5.一个表最多有12个触发器，但同一时间，同一事件，同一类型的触发器只能有一个。各触发器之间不能有矛盾。一个表上的触发器越多，对该表的DML操作影响越大

2.触发器的分类：

　　DML触发器：DML操作时触发的。

　　替代触发器：针对视图的。

　　数据库事件触发器：执行数据库系统操作时激活。

3.触发器的构成

　　触发事件：当执行什么事件时激活触发器，如执行update、delete操作时

　　触发时间：在执行事件之前激活触发器还是执行事件之后激活触发器

　　触发对象：操作哪儿张表时激活触发器（触发器创建后存放在表中，相当于定义触发器针对的表对象）

　　触发操作：当激活触发器时，要执行的操作，如（DML操作或报异常）

4.创建触发器：

-- 创建触发器
create or replace trigger 触发器名
  before|after|instead of 触发时间
  on 触发对象 表|视图 (for each row)
  begin
    触发操作
  end;

-- 创建一个简单的触发器，不允许修改表数据
create or replace trigger tg1
  --当有多个操作时，用or连接，如before update or insert or delete
  -- 使用of关键字可以定位到列，也可以不写，表示整张表
  before update
  on emp
begin
  --  触发器执行的代码块，
  raise_application_error(-20002, '不允许修改表数据');--定义一个异常，当触发器激活时，发生异常，操作失败回滚
end;
-- 执行修改语句测试
update emp set sal=100 where DEPTNO=10;

-- 创建一个触发器，当对emp表执行增\改操作时，对工资列设置触发器
create or replace trigger tg1
--   使用of关键字，定位到列
  before insert or update of sal
--   for each row 关键字，如果不写，表示是表级触发器，写了就表示是行级触发器
  on EMP for each row
  begin
--     inserting |updating |deleting 判断当前操作是哪种操作
    if inserting then--录入操作
      if :new.sal is null then
        raise_application_error(-20390,'工资不能为空');
      end if;
      elsif updating then
--         使用 ：old|：new 表示旧的数据及新的数据，下面判断语句意思为，如果原来的工资大于修改后的工资，抛异常
--         :old|:new 操作不能在表级触发器中
      if :old.sal>:new.SAL then
        raise_application_error(-20389,'不能降工资');
      end if;
    end if;
  end;
--测试触发器
insert into emp(empno,ename) values(8003,'张三');
update emp set sal=500 where empno=8001;

for each row:表示当前的触发器为行级触发器，行级触发器与表级触发器的区别是：行级触发器要求，当操作语句影响多行时，要求每一行的数据，只要符合约束条件，均激活一次触发器；而表级触发器，只要操作的语句满足约束条件，只激活一次触发器。

-- 行级触发器及表级触发器的区别：
-- 创建行级触发器
create or replace trigger tg2
  before update
  on emp for each row
begin
  dbms_output.put_line('修改了数据');
end;
-- 测试，测试结果为部门20有多少员工，就输出多少遍‘修改了数据’
update emp set comm=nvl(comm,0)+2000 where DEPTNO=20;

-- 创建表级触发器,为了防止触发器间相互影响，因此都用同一个名字，使用or replace来替换
create or replace trigger tg2
  before update
  on emp
begin
  dbms_output.put_line('修改了数据');
end;
-- 再次测试，测试结果为不论部门20有多少员工，只输出一次‘修改了数据’，即触发器只激活了一次
update emp set comm=nvl(comm,0)+2000 where DEPTNO=20;

：old 行数据，表示旧的数据（update、delete之前）

：new 行数据， 表示新的数据（insert、update之后）

after触发器

-- 创建员工表的历史表，执行删除操作后，将删除的数据存入历史表中
create table emp_his as select * from emp where 1=2;
select * from emp_his;
-- 创建触发器，执行删除操作后，将删除的数据存入历史表
create or replace trigger tg3
  after delete on emp for each row
begin
--   将数据存入历史表
  insert into emp_his(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 EMP where empno=8001;
select * from emp_his;

替代触发器

替代触发器通常与视图一起使用，当我们在查询数据时，有时需要联合多张表一块查询，这样每次查询都要重新编写语句将表联合起来，那么能不能将联表后查询到的数据放入同一个表格中，方便查看呢？视图就是这样的一个表，将查询到的数据存入视图中，这样我们需要查询时，直接查询视图就可以了，避免了多次联表查询。

因为视图是多张表的联合数据，因此只能进行查询操作，不能进行添加，修改，删除等， 因为这些数据都是不同的表中的数据，要操作的话，要进入不同的表中进行操作，要想在视图中进行增删改等操作，就要用到替代触发器

-- 首先使用管理员登录，给用户scott分配创建视图的权限
grant create view to scott;
-- 创建视图
create or replace view view1 as
select empno, ename, EMP.DEPTNO, dname
from emp,dept
where emp.DEPTNO = DEPT.DEPTNO;

select * from view1;
insert into view1 values(8001,'zhangsan',50,'人事部');
-- 创建触发器
create or replace trigger tg4
  instead of insert on view1 for each row
begin
  insert into dept(deptno, dname)values (:new.DEPTNO,:new.dname);
  insert into EMP(empno, ename, deptno) values (:new.empno,:new.ename,:new.DEPTNO);
end;
-- 关闭之前创建的触发器，避免对该触发器产生的影响
ALTER TRIGGER tg1 disable ;
insert into view1 values(8001,'zhangsan',50,'人事部');

数据库事件触发器：

执行相应的数据库事件时会激活触发器

-- 模拟创建实验表，数据库触发器创建需要管理员权限
create table login_user_table(
  username varchar2(20),
  ip varchar2(20)
);

create or replace trigger tg4
  after logon on database
begin
  insert into login_user_table values (ora_login_user, ora_client_ip_address);
end;
-- 切换不同的用户登录
select * from login_user_table;

 

转载于:https://www.cnblogs.com/Zs-book1/p/11238975.html