PL/spl之触发器

触发器作用

当什么事件发生时，执行某段代码。

代码示例

create or replace trigger text_trigger
after delete --在delete操作以后进行处理   after在操作之后进行操作  before 在进行操作之前操作  instead of 替代操作
on student
for each row
declare
	--vsid sc.sid&type  创建一个变量 vsid 变量类型为SC.sid的类型
begin
	--new为新数据  使用前加  ：
	--old是旧数据  使用前加  ：
	insert into SC (cid,sid,Score) values ('01'，:new.sid,99);
	insert into SC (cid,sid,Score) values ('02'，:new.sid,99);
	insert into SC (cid,sid,Score) values ('03'，:new.sid,99);
end;

触发器可响应的事件有：

•对表进行的INSERT、UPDATE及DELETE操作 （可以对插入，删除，更新进行监听）
•对视图进行类似的操作（通过替换监听 可以实现视图的数据操作）
•数据库的启动与关闭等

触发器主要分类：

•DML触发器insert、update、delete
•DDL触发器create、drop、alter
•系统级触发器dbstart、dbshut、logOn、grant等…（如：关机前对数据库进行操作）

new与old

:new
当insert时，:new对象中存储的为当前待插入的数据
当update时，代表着新数据
:old
操作完成前的数据值 当update时，代表着老数据
当delete时，代表着老数据

使用触发器去修改视图的数据

使用替代 intead of

create or replace view V_student as select student.* ,sc.cid ,sc.score from student ,sc where student.sid = sc.sid;
--创建视图
select * from V_student;
--查看视图
create or replace trigger trgger_V_student_insert --创建触发器
instead of insert   --使用替换
on V_student
for each row
  begin 
    insert into student values(:new.sid,:new.sname,:new.sage,:new.ssex);
    insert into sc values(:new.sid,:new.cid,:new.score);
  end;
  
  insert into V_student values('12', '安翌', '2010-01-01', '男', '01', 99);
  --插入输入
 select * from V_student;
 --查看

测试数据

create table Student(SID varchar(10),Sname nvarchar(10),Sage varchar2(),Ssex nvarchar(10));
*/
–创建测试数据

insert into Student values(‘01’ , ‘赵雷’ , ‘1990-01-01’ , ‘男’);
insert into Student values(‘02’ , ‘钱电’ , ‘1990-12-21’ , ‘男’);
insert into Student values(‘03’ , ‘孙风’ , ‘1990-05-20’ , ‘男’);
insert into Student values(‘04’ , ‘李云’ , ‘1990-08-06’ , ‘男’);
insert into Student values(‘05’ , ‘周梅’ , ‘1991-12-01’ , ‘女’);
insert into Student values(‘06’ , ‘吴兰’ , ‘1992-03-01’ , ‘女’);
insert into Student values(‘07’ , ‘郑竹’ , ‘1989-07-01’ , ‘女’);
insert into Student values(‘08’ , ‘王菊’ , ‘1990-01-20’ , ‘女’);

create table Course(CID varchar(10),Cname nvarchar(10),TID varchar(10));
insert into Course values(‘01’ , ‘语文’ , ‘02’);
insert into Course values(‘02’ , ‘数学’ , ‘01’);
insert into Course values(‘03’ , ‘英语’ , ‘03’);

create table Teacher(TID varchar(10),Tname nvarchar(10));
insert into Teacher values(‘01’ , ‘张三’);
insert into Teacher values(‘02’ , ‘李四’);
insert into Teacher values(‘03’ , ‘王五’);

create table SC(SID varchar(10),CID varchar(10),score decimal(18,1));
insert into SC values(‘01’ , ‘01’ , 80);
insert into SC values(‘01’ , ‘02’ , 90);
insert into SC values(‘01’ , ‘03’ , 99);
insert into SC values(‘02’ , ‘01’ , 70);
insert into SC values(‘02’ , ‘02’ , 60);
insert into SC values(‘02’ , ‘03’ , 80);
insert into SC values(‘03’ , ‘01’ , 80);
insert into SC values(‘03’ , ‘02’ , 80);
insert into SC values(‘03’ , ‘03’ , 80);
insert into SC values(‘04’ , ‘01’ , 50);
insert into SC values(‘04’ , ‘02’ , 30);
insert into SC values(‘04’ , ‘03’ , 20);
insert into SC values(‘05’ , ‘01’ , 76);
insert into SC values(‘05’ , ‘02’ , 87);
insert into SC values(‘06’ , ‘01’ , 31);
insert into SC values(‘06’ , ‘03’ , 34);
insert into SC values(‘07’ , ‘02’ , 89);
insert into SC values(‘07’ , ‘03’ , 98);