/***********************触发器****************
触发器:
触发器分为 ：before |after |instead of
语法：
create [or replace] trigger 触发器名称
before|after|instead of
insert [or] update [of 字段] [or] delete
on 表名或者试图名称
[for each row]----:new :old
[when 触发器条件]
触发器：行级触发器和语句级触发器
行级触发器：:new ----将要添加的新数据
:old ----即将要删除的数据
在insert数据可以使用 :new
在delete数据可以使用 :old
在update数据可以使用 :new :old
注意：
1.在触发器中不能使用事务控制语句(commit rollback )
2.在语句级的触发器，不管影响数据条数是多少，仅仅只激活一次触发器
3.intead of触发器只能用在试图上
*************************************************/
-----当前有一张学生信息表 stuid stuname ,当每次删除数据，要求备份对应的数据
create table stuinfo
(
stuid number(4) primary key,
stuname varchar2(100)
)
insert into stuinfo values(1001,'张三');
insert into stuinfo values(1002,'李四');
---1.创建备份表，其结构与学生表相同
create table stuinfoback as select * from stuinfo where 1=2;
---2.创建触发器，完成当删除学生信息，向stuinfoback表插入数据
create or replace trigger tri_stuinfo
before delete on stuinfo
for each row
begin
---向stuinfoback表插入数据
insert into stuinfoback values(:old.stuid,:old.stuname);
commit;
end;
---测试
delete from stuinfo where stuid=1001
select * from stuinfo;
select * from stuinfoback;
-----当添加学生信息，备份表中也相应添加对应的数据，当我们修改学生，备份表中的信息也随之修改
create or replace trigger tri_stuinfo
after
insert or update of stuname or delete
on stuinfo
for each row
begin
----当添加学生信息
if inserting then
dbms_output.put_line('insert');
insert into stuinfoback values(:new.stuid,:new.stuname);
elsif updating then
dbms_output.put_line('update');
update stuinfoback set stuname=:new.stuname where stuid=:old.stuid;
elsif deleting then
dbms_output.put_line('delete');
delete from stuinfoback where stuid =:old.stuid;
end if;
end;
---测试
insert into stuinfo values(1003,'张飞');
insert into stuinfo values(1004,'关云长');
update stuinfo set stuname='kkkk' where stuid in(1003,1004);
delete from stuinfo where stuid in(1003,1004);
commit;
----语句级触发器
create or replace trigger tri_stu
after update
on stuinfo
begin
dbms_output.put_line('语句级别触发器被调用');
end;
---有条件触发器
create or replace trigger tri_stuinfo
after
insert or update of stuname or delete
on stuinfo
for each row
---触发器的条件
when (new.stuid > 1005)---没有冒号
begin
----当添加学生信息
if inserting then
dbms_output.put_line('insert');
insert into stuinfoback values(:new.stuid,:new.stuname);
elsif updating then
dbms_output.put_line('update');
update stuinfoback set stuname=:new.stuname where stuid=:old.stuid;
elsif deleting then
dbms_output.put_line('delete');
delete from stuinfoback where stuid =:old.stuid;
end if;
end;
insert into stuinfo values(1006,'xiaoyi');
----学生信息表
create table stuinfo
(
stuid number(4) primary key,
stuname varchar2(100)
)
----学生成绩表
create table stuscore
(
sid number(4) primary key,
subject varchar2(100),
stuid number(4) references stuinfo(stuid),
score int
)
insert into stuscore values(1,'html',1001,90);
insert into stuscore values(2,'oracle',1001,100);
insert into stuscore values(3,'html',1002,80);
insert into stuscore values(4,'oracle',1002,60);
---创建试图 stuid stuname subject score
create or replace view v_stuinfo
as
select sid,stuinfo.stuid,stuname,subject,score from stuinfo
inner join stuscore on stuinfo.stuid=stuscore.stuid
select * from v_stuinfo;
---通过试图修改学生信息（不成功）
update v_stuinfo set stuname='kkk' where sid=1;
---通过学生信息表来修改学生信息（成功？）
update stuinfo set stuname='kkk' where stuid=1001;
create or replace trigger tri_v_stuinfo
instead of update on v_stuinfo
for each row
begin
update stuinfo set stuname=:new.stuname where stuid=:old.stuid;
end;
---触发器管理
---禁用
alter trigger tri_stuinfo disable;
---激活
alter trigger tri_stuinfo enable;
----自主性事务
--子存储过程
create or replace procedure pro_sub
is
mysal emp.sal%type;
---自主事务声明
--pragma autonomous_transaction;
begin
select sal into mysal from emp where empno=7369;
dbms_output.put_line('在子过程中：'||mysal);
---事务回滚
rollback;
end;
----父存储过程
create or replace procedure pro_parent
is
mysal emp.sal%type;
begin
select sal into mysal from emp where empno=7369;
dbms_output.put_line('修改之前：'||mysal);
---修改
update emp set sal=888 where empno=7369;
---调用子存储过程
pro_sub();
select sal into mysal from emp where empno=7369;
dbms_output.put_line('修改之后：'||mysal);
end;
--测试
begin
pro_parent();
end;
