触发器

------------------------------------环境代码

create  table student
(stuid varchar2(10) not null,
stuname varchar2(10) not null,
sex char(2)
);

create table subject
(subjectid int,
subjectname varchar2(10)
);

create table score
(
stuid int,
subjectid int,
score int
);

 

insert into student values (1001, 'wind', '男');
insert into student values (1002, 'snow', '女');
insert into student values (1003, 'apple', '男');
insert into subject values (1, 'oracle');
insert into subject values (2, 'java');
insert into score values (1001, 1, 90);
insert into score values (1002, 2, 88);

---------------主体部分

一、触发器
1.触发器具有三个部分
(1).触发事件
(2).可选的触发器约束条件
(3).触发器动作

2.可以新建对应如下语句的触发器
(1).DML语句(insert、delete、update)
(2)DDL语句（create、alter、drop）
(3).数据库操作（servererror、logon、logoff、startup、shutdown）

3.可以创建触发器的对象：1.数据库表 2.数据库视图 3.用户模式 4.数据库实例

4.触发器类型
(1).DML触发器（包括行级触发器、语句级触发器）
(2).系统触发器
(3).替代触发器
(4).模式触发器

5.执行DML语句的顺序
(1).执行before语句级触发器（如果存在）
(2).对于受语句影响的每一行执行DML语句
(3).执行after语句触发器（如果存在）

6.两个特殊的值
:new 新产生的值
:old 原是的值

7.触发器中的谓语
(1). inserting
(2).updating
(3). deleting

 

二、新建DML语句

create or replace trigger schemaname.triggername
before | after |delete |update of 列名
on 表名
[for each row]
when 条件

----------------
----------------第一部分：before触发器---------------
---------------

--案例01：新建一个测试的行前触发器
create or replace trigger tr01
before insert on student
begin
  dbms_output.put_line('这个是行前触发器！');
end;

--案例02：新建一个测试的行后触发器
create or replace trigger tr02
after update on student  /*没有for each row说明是个表级别触发器*/
begin
  dbms_output.put_line('这个是表级update触发器！');
/*如果你一次更新的语句是几条只触发一次！*/
end;

--案例03：新建一个测试的行后触发器：行级触发器

/*行级触发器即每操作一行都会触发一次触发器*/
create or replace trigger tr02
after update on student
for each row  /* each row说明是个行级别触发器*/
begin
  dbms_output.put_line('这个是行级update触发器！');
/*操作一条语句就触发一次这个触发器！*/
end;

--案例04：新建一个使用属性:new的insert触发器
create or replace trigger tr01
before insert on student
for each row   --必须是行级触发器
begin
  dbms_output.put_line('这个是行前触发器！');
  dbms_output.put_line('new id:' || :new.stuid);
  dbms_output.put_line('student name:' || :new.stuname);
  dbms_output.put_line('student sex:'  || :new.sex);
end;

--案例05：新建一个使用属性:old的delete触发器
create or replace trigger tr01
before delete on student
for each row   --必须是行级触发器
begin
  dbms_output.put_line('这个是行前触发器！');
  dbms_output.put_line('old id:  ' ||:old.stuid);
  dbms_output.put_line('old name:  ' ||:old.stuname);
  dbms_output.put_line('old sex:  ' ||:old.sex);
end;

--案例06：新建一个使用属性:old的update触发器
create or replace trigger tr01
before update on student
for each row   --必须是行级触发器
begin
  dbms_output.put_line('这个是行前触发器！');
  dbms_output.put_line('old id:'  || :old.stuid);
  dbms_output.put_line('old name:'  || :old.stuname);
  dbms_output.put_line('old sex:'  || :old.sex);
dbms_output.put_line('new id:' || :new.stuid);
  dbms_output.put_line('new name:' || :new.stuname);
  dbms_output.put_line('new sex:' || :new.sex);
end;

--案例07：新建一个使用条件的触发器
create or replace trigger tr01
before delete on student
for each row   --必须是行级触发器
when (old.sex='男')  /*这里old前面不能使用冒号只有在主体部分才允许使用冒号*/
begin
  dbms_output.put_line('这个是行前触发器！');
  dbms_output.put_line('old id' ||:old.stuid);
  dbms_output.put_line('old name' ||:old.stuname);
  dbms_output.put_line('old sex' ||:old.sex);
end;

--案例08：新建一个混合触发器01
(1)新建一个表

create table deltable as select * from student where stuid='';

(2)新建一个混合触发器
create or replace trigger tr03
before insert or update or delete on student
for each row
begin
 if inserting then
                 insert into deltable values(:new.stuid, :new.stuname, :new.sex);
                end if;
 if deleting then
   insert into deltable values (:old.stuid, :old.stuname, :old.sex);
 end if;         
exception
 when others then
dbms_output.put_line('不可预知的错误！');
end;

--案例09：新建一个混合触发器02
(1)新建一个表

create table deltable as select * from student where stuid='';

create table updtable as select * from student where stuid='';

create table intable as select * from student where stuid='';

(2)新建一个混合触发器
create or replace trigger tr03
before insert or update or delete on student
for each row
begin
 if inserting then
                 insert into intable values(:new.stuid, :new.stuname, :new.sex);
                end if;
 if deleting then
   insert into deltable values (:old.stuid, :old.stuname, :old.sex);
 end if;     
 if updating then
                     insert into updtable values(:new.stuid, :new.stuname, :new.sex);
                     insert into updtable values (:old.stuid, :old.stuname, :old.sex);
 end if;
exception
 when others then
dbms_output.put_line('不可预知的错误！');
end;

 

--案例10：新建一个混合触发器03
(1)新建一个表

create table deltable as select * from student where stuid='';

create table updtable as select * from student where stuid='';

create table intable as select * from student where stuid='';

(2)新建一个混合触发器
create or replace trigger tr03
before insert or update or delete on student
for each row
begin
 if inserting then
                 insert into intable values(:new.stuid, :new.stuname, :new.sex);
                 dbms_output.put_line('数据插入成功！');
                end if;
 if deleting then
   insert into deltable values (:old.stuid, :old.stuname, :old.sex);
   dbms_output.put_line('数据删除成功！');
 end if;     
 if updating then
                     insert into updtable values(:new.stuid, :new.stuname, :new.sex);
                     insert into updtable values (:old.stuid, :old.stuname, :old.sex);
      dbms_output.put_line('数据更新成功！');
 end if;
exception
 when others then
dbms_output.put_line('不可预知的错误！');
end;

----------------------------
----------------------------第二部分：after触发器
-------------------------------

 

--案例11：新建一个after触发器

--下面是库存表
create table goodstore
(
goodsno int primary key, --库存商品编号
goodscount int  --库存商品数量
);

--下面是商品出库表
create table goodstoreout
(
goodsno int,
goodcount int,
goodoutdate date default sysdate
);

insert into goodstore values (1001, 100);
insert into goodstore values (1005, 200);
insert into goodstore values (1002, 300);
insert into goodstore values (1003, 500);
insert into goodstore values (1004, 600);

--编号为1001的商品出库20件
insert into goodstoreout values (1001, 20, default);

--查询出库表
select * from goodstoreout;

--查询库存表
select * from goodstore;  --发现1001数据量有减少

--定义触发器
create or replace trigger tr04
after insert on goodstoreout
for each row
begin
  update goodstore set goodscount=goodscount-:new.goodcount
  where goodsno=:new.goodsno;
  dbms_output.put_line('本次出库的商品编号：' ||:new.goodsno || '   ' || '出库的数量为：'   ||to_char(:new.goodcount));
end;

 

--案例12：替代触发器
1)替代触发器子能应用于行级
A.新建视图
create or replace view v01
as
select stu.stuid, stu.stuname, sub.subjectid, sub.subjectname,
sc.score from student stu, subject sub, score sc
where stu.stuid=sc.stuid and sc.subjectid=sub.subjectid;

B、新建替代触发器
create or replace trigger tr02
instead of insert on v01
referencing new n
for each row
declare
  icount int;
begin
select count(*) into icount from student where stuid=:n.stuid;
if icount=0 then
  insert into student (stuid, stuname) values (:n.stuid, :n.stuname);
else
  update student set student.stuname=:n.stuname where stuid=:n.stuid;
end if;
select count(*) into icount from subject where subjectid=:n.subjectid;
if icount=0 then
  insert into subject values (:n.subjectid, :n.subjectname);
else
  update subject set subject.subjectname=:n.subjectname where   subjectid=:n.subjectid;
end if;
select count(*) into icount from score where subjectid=:n.subjectid and stuid=:n.stuid;
if icount=0 then
  insert into score values (:n.stuid, :n.subjectid, :n.score);
else
  update score set score.score=:n.score where subjectid=:n.subjectid and stuid=:n.stuid;
end if;
end;

C.测试

 

--案例13：变异表的演示
--1.新建表
create table emp_tab as select * from emp;

--2.新建一个触发器

create or replace trigger emp_count
after delete on emp_tab
for each row
declare
  n integer;
begin
  select count(*) into n from emp_tab;
  dbms_output.put_line('there are now' || n );
end;

 

--案例14：维护触发器
alter triger tr01 disable;
alter trigger tr01 enable;

-----------
----------第三部分：内置程序包
----------

--案例15：验证dbms_output设置选项
SQL>set serveroutput on
SQL> set serveroutput on
SQL> set serveroutput on size 5000

 declare
   str varchar2(20):='hello world!';
  begin
  dbms_output.put(str);  /*结果屏幕上不会显示任何东西，dbms_output.put不具备显示功能*/
  end;
 

 declare
  str varchar2(20):='hello world!';
  begin
  dbms_output.put(str);
  dbms_output.put_line('good');--具有显示功能
  end;
 
/*这里面由于有两次将变量值保存到缓存中，所以打印时将缓存里面所有的值显示出来*/

--案例16-02：演示DBMS_LOB使用

--01.新建表
create table downfilelist
(
id varchar(20) not null primary key,
name varchar(40) not null,  /*文件存放位置*/
filelocation bfile,  /*文件描述*/
description clob
)；

--02.新建需要的目录
/*
新建目录格式：；
create or replace directory  目录名 as  ‘本地或网络共享路径名’；
*/

create or replace directory filedir as 'f:/downlist' ；
--或
create or replace directory filedir as '//servername/downlist' ；

--03.对表的中数据操作
-a.插入数据到表中，对bfile类型的字段使用bfilename函数
insert into downfilelist values ('00001', 'java入门', bfilename(upper('filedir'), 'java.zip'), '这是一本使你快速学会的java教材');

-b.更新lob
select description from downfilelist where id='00001';

update downfilelist set description ='这是一本使你快速学会oracle的书', filelocation=bfilename(upper('filedir'), 'oracle.zip')
where id='00001';

select id, name, description from downfilelist;  /*在sqlplus中需要这样写否则无法显示或则不能写：
select * from downfilelist;
*/

--案例16-02：演示DBMS_LOB使用：read

declare
tempdesc clob;
ireadcount int;
istart int;
soutputdesc varchar(100);
begin
  ireadcount:=5;
  istart:=1;
  select description into tempdesc from downfilelist where id='00001';
dbms_lob.read(tempdesc, ireadcount, istart, soutputdesc); /*从istart取值，取ireadcont个*/
dbms_output.put_line('前五个字符是：'||soutputdesc);
end;

--案例16-03：演示DBMS_LOB使用：getlength
/*统计字符串长度*/
declare
  tempclob clob;
  ilen int;
begin
select description into tempclob from downfilelist where id='00001';
ilen:=dbms_lob.getlength(tempclob);
dbms_output.put_line('描述的字段长度为：'||ilen);
exception
  when others then
   dbms_output.put_line(sqlcode || '  ' ||sqlerrm);
end;

--案例16-03：演示DBMS_LOB使用：write

declare
  tempdesc clob;
  icount int;
  istart int;
  snewvar  varchar2(30);
begin
  icount:=2;
  istart:=1;
  snewvar:='这是非常畅销的书';
  select description into tempdesc from downfilelist
   where id='00001' for update;
   /*修改大数据时可能速度很慢所以锁定以便提高速度*/
dbms_output.put_line('更改前：:'||tempdesc);
dbms_output.put_line('从' ||istart || '开始的'||icount ||'个字符将被改写成：'||snewvar);
dbms_lob.write(tempdesc, icount, istart, snewvar);
commit;  /*修改完数据后需要解锁所以提交否则别的用户不能使用数据*/
end;

select * from downfilelist where id=00001;