Oracle触发器

一、触发器(原理)

1.三种操作： delete update insert

2.两个临时表： :new :old

3.两个时间点： after before

4.两种重要模式： 行级 语句级

5.条件： when

二，什么是触发器？

1.触发器是一种特殊的存储过程；

2.触发器不能被直接调用，也不是手动启动，而是由事件来触发；–增删改

3.触发器经常用于加强数据的完整性约束和业务规则等。

4.触发器具备事务的功能。简单点说：触发器就是个开关，负责灯的亮与灭，你动了它就亮了。

三，创建触发器语法

create [or replace] trigger trigger_name
after|before|instead of                    --instead反向
[insert][[or] update [of 列列表]][[or] delete]
on table表或view                            --视图使用率 
[referencing{:old [as] old/:new [as] new}]  --引用新表老表
[for each row]                              --行级模式
[when(condition)]                           --条件
pl/sql_block;                               --pl/sql语句(begin...end)

四，案例

案例所需脚本

--创建学生信息表
create table stuInfo
(
  stuNo varchar2(8) not null primary key,
  stuName varchar2(10) not null,
  stuSex varchar2(2) not null,
  stuAge number(6) not null,
  stuSeat number(6) not null,
  strAddress varchar2(255) default('地址不详')
)
 
go
 
--创建学生成绩表
create table stuMarks
(
  ExamNo varchar2(7) not null primary key,
  stuNo varchar2(6) not null references stuInfo(stuNo),
  writtenExam number(6) null,
  LabExam number(6) null
)
 
go
 
--往学生信息表内插入测试数据
insert into stuInfo(stuNo, stuName, stuSex, stuAge,stuSeat,strAddress)
select 's25301', '张秋丽', '男', 18,1, '北京海淀' from dual union
select 's25303', '李斯文', '女', 22,2, '河阳洛阳' from dual union
select 's25302', '李文才', '男', 85, 3,'地址不详' from dual union
select 's25304', '欧阳俊雄', '男', 28, 4,'新疆' from dual union
select 's25318', '梅超风', '女', 23, 5,'地址不详' from dual
 
go
 
--往学生成绩表内插入测试数据
insert into stuMarks(ExamNo, stuNo, writtenExam, LabExam)
select 's271811', 's25303', 93, 59 from dual union
select 's271813', 's25302', 63, 91 from dual union
select 's271816', 's25301', 90, 83 from dual union
select 's271817', 's25318', 63, 53 from dual
 
go
 
select * from stuinfo;
select * from stumarks;

案例一 要求李斯文不能删除

1.要求李斯文不能删
create or replace trigger ta
after delete --执行删除后触发
on stuinfo --触发器所在的表
for each row --行级
begin
  --可执行部分
  if(:old.stuname='李斯文') then
    raise_application_error(-20001,'李斯文不能删除');
  end if;
end;
 
--验证
delete from stuinfo where stuname='李斯文'
--核对
select * from stuinfo;

案例二 张三不能增加

create or replace trigger tb
after insert
on stuinfo
for each row
begin
  if(:new.stuname='张三') then
    raise_application_error(-20002,'张三不能增加');
  end if;
end;
 
--验证
insert into stuinfo(stuno,stuname,stusex,stuage,stuseat,straddress)
values('s25339','张三','女',38,38,'西班牙')
 
--核实
select * from stuinfo;

案例三 李斯文不能修改

create or replace trigger tc
after update
on stuinfo
for each row
begin
  if(:old.stuname='李斯文') then
    raise_application_error(-20003,'李斯文不能修改');
  end if;
end;
 
--验证
update stuinfo set stuname='羊癫疯' where stuname='李斯文'
 
--核实
select * from stuinfo;

案例四 合三为一（把上面的触发器合成一个触发器）

create or replace trigger td
after insert or delete or update
on stuinfo
for each row
begin
  case
    when inserting then --当正在执行增加操作的时候
      if(:new.stuname='张三') then
        raise_application_error(-20002,'张三不能加');
      end if;
    when deleting then  --当正在执行删除操作的时候
      if(:old.stuname='李斯文') then
        raise_application_error(-20001,'李斯文不能删');
      end if;
    when updating then  --当正在执行修改操作的时候
      if(:old.stuname='李斯文') then
        raise_application_error(-20003,'李斯文不能改');
      end if;
  end case;
end;

案例五 级联删除（删除李斯文的同时成绩自动删除）

create or replace trigger ta
after delete
on stuinfo
for each row
declare
sno varchar2(6);--装李斯文的学号
begin
  if(:old.stuname='李斯文') then
    sno := :old.stuno;--给sno赋值
    delete from stumarks where stuno=sno;--把李斯文对应的成绩删除
  end if;
end;
 
--验证
delete from stuinfo where stuname='李斯文'
--核实
select * from stuinfo;
select * from stumarks;

案例六 标识列：序列+触发器

--序列
create sequence seq_0220;
--表:tb_0220(sid,sname,ssex)
create table tb_0220 (
       sid number primary key,
       sname varchar2(20) not null,
       ssex varchar2(10) check(ssex='男' or ssex='女')
)
--触发器：前触发
create or replace trigger tri
before insert --注意：前触发
on tb_0220
for each row
begin
  :new.sid := seq_0220.nextval;--增加之前拿到序列的下一个值给临时表的学号赋值
end;
 
--验证
insert into tb_0220(sname,ssex)
values('王校长','女')
 
--核实
select * from tb_0220