触发器，函数，包都是可以再生利用的东西，所以在创建的时候都要用到create or replace这个万能语句，接着就是主角trigger的出现了，主角出现还需要一点点波动，通常大人物都不是随随便便就显现的，所以要配上before [after] insert[update/delete] on tableName.
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
/*完成：
、当向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;
/*
、当删除SC表中数据时，修改（或添加）SC_NUMBER中的数据。*/
insert into sc values('','C01','');
delete from sc where sno='' and cno='C01'
select * from sc where sno=''
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;
/*
、当修改SC表中数据时，若修改的是学号，则对应修改SC_NUMBER表中的选课门数，
否则打印“某某（学生姓名）的学生选课信息已经修改”信息。*/
insert into sc values('','C01','');
delete from sc where sno='' and cno='C01'
select * from sc where sno=''
select * from sc where sno=''
select * from SC_Number;
update sc set sno='' where sno=''
update sc set grade=90 where sno='' 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='';
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;/*
、不能删除90分以上学生的选课信息。*/
update sc set grade=98 where sno='' and cno='C10'
delete from sc where sno='' 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('','Shawn',21,'m','CS');
delete from student where student.sno='';
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=''
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=''
select * from scselect * from student where student.sno=''
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;
