Oracle的触发器学习笔记

触发器的概述、分类
（1）特殊的存储过程，自动执行
（2）触发器是不能带参数的（与视图类似）
（3）保证表的完整性约束

--触发器的特点：
                （1）是一个特殊的存储过程
                （2）由系统自动调用，不能显示去调用触发器
                （3）创建触发器的时候就指定了什么时候去调用触发器
                
--替代触发器：
建立在视图上的触发器叫做替代触发器，他是行级触发器。
                
--创建触发器的语法
create or replace trigger trig_name
after|before   insert|update|delete on 表名  --当你对某个表进行增删改操作的时候调用触发器
for each row --行级触发器：每删除一行就会触发一行，还有一种触发器叫做语句集触发器：这个就是不管影响到多少行，他只会触发一次。
begin          
      --触发器要做的事情
end;

select * from dept;

insert into dept values(9,'测试部','北京');

--创建一个触发器
create or replace trigger trig_test
after insert on dept --当我们对dept表进行插入操作的时候去触发这个触发器
for each row --行级触发器
--指定要处理的过程
begin
    --限制部门地址只能位于‘长沙’
    if :new.loc <>'长沙' then
       dbms_output.put_line('您输入地址不是长沙,不能插入');
       --rollback; --回滚
    else
       dbms_output.put_line('插入成功'); 
       --commit;
    end if;
end;


--注意：不能在触发器中间使用rollback,commit;

select * from stu;

insert into stu values(1,'张三');

--在stu表上建立一个触发器，当往stu表中插入数据的时候，显示插入进去的数据
create or replace trigger trig_stu
after insert on stu --对stu表插入数据的时候触发触发器
for each row --行级触发器
begin
    --显示插入进去的数据
    dbms_output.put_line('学号为：'||:new.sno||'姓名为：'||:new.sname);
end;

<1>行级触发器的工作原理
都是针对delete,update,insert而言

行级触发器的设计应用

drop table student;
drop table score;
--创建学生表
create table student
(
sno number(3),-- 学号
sname varchar2(20)--姓名
)
--创建score表
create table score
(
sno number(3),--学号
cname varchar2(20),--课程名
score float--成绩
)
--插入测试数据
insert into student values(1,'张3');
insert into student values(2,'张4');
insert into student values(3,'张5');
insert into student values(4,'张6');
insert into student values(5,'张7');
--插入测试数据
insert into score values(1,'语文',87);
insert into score values(2,'语文',80);
insert into score values(3,'语文',85);
insert into score values(4,'数学',97);
insert into score values(5,'外语',82);
insert into score values(4,'Java',77);
insert into score values(1,'数据库',99);
insert into score values(2,'数学',77);
insert into score values(3,'外语',67);

select * from student;
select * from score order by sno;

--创建触发器（当删除某个学生的时候，同时去删除成绩表中此学生的信息）
create or replace trigger del_studentNo
after delete on student --什么时候去触发
for each row --行触发器
begin
    delete from score where sno=:old.sno;
end del_studentNo;  --del_studentNo 触发器的名字，可以省略
/

select * from student;
select * from score;

--删除触发器
drop trigger del_studentNo;


--验证触发器是否生效
delete from student where sno='1';


--回滚操作
rollback;


--创建一张员工表（员工编号，员工姓名），在创建一般退休表（表结构与员工表一样），
--当删除员工表的某条记录的时候，要自动将删除的记录保存到退休表。
drop table employee;
--创建一张员工表（员工编号，员工姓名）
create table employee
(
       eno number(10),--员工编号
       ename varchar2(20) --员工姓名
)
--创建退休表(通过已经存在的表创建新表)
create table employeebak
as
select * from employee where 1=0;


select * from employee;
select * from employeebak;
--插入测试数据
insert into employee values(1,'张三');
insert into employee values(2,'李四');


--创建触发器
--当删除员工表的某条记录的时候，要自动将删除的记录保存到退休表。
create or replace trigger del_employee
after delete on employee
for each row
begin
    insert into employeebak values(:old.eno,:old.ename);
end;


--测试触发器
delete from employee where eno =2;

drop table bank;
drop table tran;
--首先创建银行表
create table bank
(
       cardNo number(10),--卡号
       name varchar2(20),--姓名
       money number(20) --余额
)
--创建交易表
create table tran
(
       cardno number(10),--卡号
       type varchar2(2),--类型 1表示存， 0还是取
       money number(10),--交易的金额
       tran_date date default sysdate --交易的日期
)

--往银行表里面插入一条记录
insert into bank values('1234567890','张三',5000)


--创建触发器
create or replace trigger update_bank
after insert on tran-- 当我们往交易表中插入交易记录的时候触发这个触发器
for each row  --行级触发器
declare
   currentMoney number(10);--用来保存当前的余额
begin
    if :new.type=1 then --存款
        --更新银行表中客户的余额信息
        update bank set money = money + :new.money where cardno=:new.cardno;
    elsif :new.type=0 then --取款
        --客户的余额信息调取出来
        select money into currentMoney from bank;--查询银行表里面余额
        if currentMoney <= :new.money+10 then
           dbms_output.put_line('余额不足');
        else
            update bank set  money = money - :new.money where cardno=:new.cardno;
        end if;
    end if;
end;

select * from bank;
select * from tran;

--模拟存钱
insert into tran values('1234567890',1,2000,sysdate);
--模拟取钱
insert into tran values('1234567890',0,10000,sysdate);

--带自定义的异常
create or replace trigger update_bank
after insert on tran
for each row
declare
    currentMoney number(10);
    e exception; --异常类型的变量
begin
    if :new.type=1 then --存款
        update bank set money = money + :new.money where cardno=:new.cardno;
    elsif :new.type=0 then --取款
        select money into currentMoney from bank;--查询银行表里面余额
        if currentMoney <= :new.money+10 then
           raise e;--抛出异常
        else
            update bank set  money = money - :new.money where cardno=:new.cardno;
        end if;
    end if;
    --捕获异常
    exception 
     when e then
          dbms_output.put_line('余额不足'); 
end;




insert into tran values('1234567890',1,200,'23-3月-2003');
insert into tran values('1234567890',0,6000,'23-3月-2003');


select * from bank;


select * from tran;


delete from tran where money =6000;
--insert触发器
create or replace trigger insert_student
after insert on student
for each row
begin
    --默认往成绩表中插入一条数据
    insert into score values(:new.sno,'语文',60);
end;
/


select * from student;
select * from score;
--测试
insert into student values(6,'张8');


--解读 :OLD 和 :NEW 
:old做删除操作的时候存在于内存中的一张表
:new当我们在做插入操作的时候存在于内存中的一张表
:old 和 :new 表的结构跟触发器建立在哪个表上的这张表结构一样
insert  涉及:new表，相当于SQL Server 2005中的inserted表
delete  涉及:old表，相当于SQL Server 2005中的deleted表
update  涉及:new 和 :old 表


--update 触发器
--作用：当更新学生表的学号信息的时候，我们需要同时修改成绩表中此学生对应的学号
create or replace trigger update_student
after update on student
for each row
begin
   update score set sno = :new.sno where sno = :old.sno;
end;
/


select * from student;
select * from score;
--测试
update student set sno='8' where sno='2';
--回滚
rollback;
--注意：在触发器里面不能写commit或者rollback;


--触发器：删除学生表中的学号为8的学生的时候，提示“不允许删除学号为8的学生”
create or replace trigger delete_student
after delete on student
for each row
begin
if :old.sno = '8' then -- 假设我们不允许删除编号为8的学生信息
   raise_application_error(-20000,'不允许删除学号为8的学生');--错误号从-20000到-29999
end if;
end;
/


select * from student;
select * from score;
--测试
delete from student where sno = 4;--能删除
delete from student where sno = 8;--不能删除
rollback;

--行级触发器： for each row
--insert 触发器 （:new）
--update 触发器 （:new  :old）
--delete 触发器 （:old）

--扩展知识点：

<2>语句级触发器的设计应用
不涉及到数据完整性问题
如：记录哪个用户什么时候操作了哪张表？相当于日志一样
select * from mylog;
drop table mylog;
--首先创建日志表
create table mylog
(
 curr_user varchar2(100),--当前用户
 curr_date date,
 act char(1)  --是insert ,update ,delete 
)


--创建语句级触发器
create or replace trigger dml_student
after insert or delete or update on student --insert or delete or update 不管是增删改，都触发
begin
    if inserting then   --inserting 表示往学生表中插入数据
     insert into mylog values(user,sysdate,'I');
    elsif deleting then  --deleting 关键字，往学生表中删除数据
     insert into mylog values(user,sysdate,'D');
    elsif updating then 
     insert into mylog values(user,sysdate,'U'); 
    end if;
end;
/ 


select * from student;
--测试insert
insert into student values(2,'张9');
rollback;


--测试delete
delete from student where sno=2;
rollback;


--测试update
update student set sname='张2'where sno = 1;
rollback;


--查看数据
select * from mylog;
select * from student;
select * from score;




<3>替换触发器的应用


drop table autoInrement
--采用序列代替自动增长
create table autoInrement
(
       A number(5),
       B varchar2(20)
)


drop sequence auto_seq;
--创建序列
create sequence auto_seq
minvalue 1
maxvalue 1000
start with 1
increment by 1
cache 20
cycle;


--以前往表中插入记录如下
insert into autoInrement values(auto_seq.nextval,'abc');


select * from autoInrement;


--现在我们自己创建触发器来插入数据
create or replace trigger set_no
before insert on autoInrement
for each row--行级触发器
declare 
    sno number(3);--定义变量
begin 
    select auto_seq.nextval into sno from dual;
    :new.a := sno;
end;
/


--在来插入数据
insert into autoInrement values(221,'aaa');
insert into autoInrement values(232,'bbb');
--查询数据可以看到插入到表中的数据并不是我们写插入语句插入进去的数据
select * from autoInrement ;




--替换触发器能解决视图多表更新的问题的
create or replace view v_emp_dept 
as
select empno,ename,job,d.deptno,d.dname
from emp e,dept d
where e.deptno = d.deptno;
--查看视图
select * from v_emp_dept;
--查看基表
select * from emp;
select * from dept;


--直接插入数据报错
insert into v_emp_dept values('4562','test','clerk','90','开发部');


drop trigger tr_v_e_d;
--创建替换触发器(只能建立在视图上)
create or replace trigger tr_v_e_d
instead of insert on v_emp_dept  --通过视图进行插入操作的时候，调用触发器
for each row --替换触发器也是一种行级触发器
begin
    insert into dept(deptno,dname) values(:new.deptno,:new.dname);
    insert into emp(empno,ename,job,deptno) values(:new.empno,:new.ename,:new.job,:new.deptno);
end;
/

--测试触发器
insert into v_emp_dept values('456','test','clerk','50','开发部');

select * from v_emp_dept;
select * from emp;
select * from dept;

--启用和禁用触发器
ALTER TRIGGER aiu_itemfile ENABLE;
ALTER TRIGGER aiu_itemfile DISABLE;

--删除触发器
DROP TRIGGER aiu_itemfile;

--查看用户下创建了哪些触发器
select * from user_triggers;

--触发器
（1）明白增删改操作的原理，熟练使用:old和:new表
（2）触发器的特点
     它是一个特殊的存储过程，不能显示调用，是由系统自动调用
     不能在触发器中使用参数
     不能在触发器中使用rollback 以及commit;
（3）重点掌握行级触发器的使用
（4）语句级触发器以及替换触发器（特殊的行级触发器）