Oracle触发器使用介绍
create or replace
trigger is tri_update[/insert/delete/UID]_tableName
before[after] insert[update/delete/or 。。。] on tableName
[for each row]--这个一般都要加上，因为我们一般都是行级触发器，即对每一行都操作
[declare
locateParamater1 type;
locateParamater1 type;]
--又见begin...end;
begin
--do something;
end;
在给实例之前，想说说自己使用触发器的几个感受：
（1）关于new和old,在使用之前联系实际想一想吧，insert的时候肯定没有old啊，delete的时候肯定没有new啊，只有update的时候old和new同时拥有，还要指出的是new和old都是rowtype的，即可以代表相应的一行。对于new和old的实际用法，请看实例。
（2）对于when的时候，要注意里面的old和new,没有冒号。还有在写raise_application_error的时候要注意他的参赛格式（number，‘Some thing you want to say’）,其中的number是从-20000到-20999
贴代码了
/*完成：
1、当向SC表插入数据时，修改（或添加）SC_NUMBER（包括学生学号和选课门数两列）表格中的数据。*/
create or replace
trigger tr_Insert_Sc after insert on sc
for each row
begin
--insert 的时候使用：new
update SC_Number set ccount=ccount+1 where SC_Number.Sno=:new.Sno;
end;
/*
2、当删除SC表中数据时，修改（或添加）SC_NUMBER中的数据。*/
insert into sc values('008','C01','92');
delete from sc where sno='008' and cno='C01'
select * from sc where sno='008'
select * from SC_Number;
create or replace
trigger tr_Delete_Sc after delete on sc
for each row
begin
update SC_Number set ccount=ccount-1 where SC_Number.Sno=:old.Sno;
end;
/*
3、当修改SC表中数据时，若修改的是学号，则对应修改SC_NUMBER表中的选课门数，
否则打印“某某（学生姓名）的学生选课信息已经修改”信息。*/
insert into sc values('008','C01','92');
delete from sc where sno='008' and cno='C01'
select * from sc where sno='008'
select * from sc where sno='002'
select * from SC_Number;
update sc set sno='008' where sno='002'
update sc set grade=90 where sno='008' and cno='C06'
create or replace
trigger tr_Edit_SC after update on SC
for each row
declare
student_name student.sname%type;
begin
if :new.sno=:old.sno then
select sname into student_name from student where student.sno=:new.sno;
dbms_output.put_line(student_name||'Have changed');
end if;
if :new.sno<>:old.sno then
update SC_Number set ccount=ccount-1 where SC_Number.Sno=:old.Sno;
update SC_Number set ccount=ccount+1 where SC_Number.Sno=:new.Sno;
end if;
end;
/* 1、修改STUDENT表数据时，限制不能修改学生的系别（不能修改CS系学生的系别）。*/
select * from student;
update student set student.sname='XXX' where student.sno='003';
create or replace
trigger tr_Edit_Student_cons before update on student
for each row
when(old.sdept='CS')
begin
raise_application_error(-20044,'You Cannot edit the information of cs department');
end;
/*2、插入课程时，课程号以‘S’开头的课程的学分不能低于3分。*/
insert into course values('S01','Oracle',3,4)
delete from course where course.cno='S01';
select * from course;
create or replace
trigger tr_Edit_Course_cons before insert on course
for each row
when(new.credit<=5 and new.cno like 'S%')
begin
raise_application_error(-20023,'You can not insert the course name ');
end;
/*
3、不能删除90分以上学生的选课信息。*/
update sc set grade=98 where sno='003' and cno='C10'
delete from sc where sno='003' and cno='C10'
select * from sc;
create or replace
trigger tr_delete_SC before delete on SC
for each row
when(old.grade>90)
begin
raise_application_error(-20017,'My grade beyond 90,You cannot delete me');
end;
/*
插入Student表中数据时，CS系学生的年龄不能大于30岁。*/
select * from student;
insert into student values('044','Shawn',21,'m','CS');
delete from student where student.sno='044';
create or replace
trigger tr_insert_Student before insert on student
for each row
when(new.sdept='CS' and new.sage>30)
begin
raise_application_error(-20078,'I am a Cs student.My age should little than 30');
end;
/*
当修改Student表中的年龄字段时，使其只能增加，不能减少。*/
select * from student;
update student set sage=38 where sno='004'
create or replace
trigger tr_update_Student before update on student
for each row
when(new.sage<old.sage)
begin
raise_application_error(-20089,'Only can beyond the old age!');
end;
/*
删除Student表中的学生信息时，判断在SC表中该学生的平均成绩是否高于60，若高于60，
则不能删除，否则允许删除，同时删除SC表该学生对应的选课信息。*/
select round(avg(grade),2),sno from sc group by sno
delete from student where student.sno='004'
select * from sc
select * from student where student.sno='001'
create or replace
trigger tr_delete_student_cons before delete on student
for each row
declare
avg_score number(4,2);
begin
select round(avg(grade),2) into avg_score from sc where sc.sno=:old.sno;
if avg_score>70.00 then
raise_application_error(-20058,'My average score beyond 60!!!!');
else
delete from sc where sc.sno=:old.sno;
end if;
end;
