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、当向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;

（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