Oracle触发器使用介绍

触发器，函数，包都是可以再生利用的东西，所以在创建的时候都要用到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('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;
/*
、当修改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;

/*
、不能删除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;