Oracle数据库的基本操作（九）—— 触发器

一、DML触发器（语句级触发器和行级触发器）

1.语句级触发器

• 语法

  create or replace trigger 触发器名 |
  {before | after} 触发事件1 or 触发事件2 or 触发事件3
  --after:在DML操作成功执行后，再执行触发器代码；
  --before:先执行触发器代码，再执行DML操作；
  --触发事件：insert,delete,update,update of 字段名
  on 表名
  declare
    声明部分：
  begin
    执行部分：
  end;
  

• 判断是哪个事件触发

  if inserting then
    --insert事件
    ..
  elsif updating then
    --update事件
    ..
  else deleting then
    --delete事件
    ..
  end if;
  

例1 创建使用after关键字的语句级触发器

• 创建触发器

  create or replace trigger tri_Test1 after update on SC
  begin
    dbms_output.put_line('创建成功！');
  end;
  

• 测试触发器

  update SC set Grade = Grade - 10;
  

  

• 测试前的SC表中的数据
  

• 测试后的SC表中的数据
  

例2 创建使用before关键字的语句级触发器，满足禁止用户在8:00至18:00时间段之外更新Student表。

• 创建触发器

  create or replace trigger tri_Test2 before update on Student
  begin
    if to_char(sysdate,'hh24:mi') not between '08:00' and '18:00' then
      raise_application_error(-20001,'不能在非工作时间修改Student表');
    end if;
  end;
  

• 测试触发器

  update Student set Sage = Sage + 10;
  

• 测试前的Student表中的数据
  

• 测试后的Student表中的数据
  

例3 判断是哪一个触发事件

• 创建触发器

  create or replace trigger tri_Test3 before update or delete or insert on Student
  begin
    if to_char(sysdate,'hh24:mi') not between '08:00' and '16:00' then
      case
        when inserting then
          raise_application_error(-20001,'不能在非工作时间向Student表中插入数据');
        when updating then
          raise_application_error(-20001,'不能在非工作时间更新Student表中的数据');
        when deleting then
          raise_application_error(-20001,'不能在非工作时间删除Student表中的数据');
      end case;
    end if;
  end;
  

• 测试触发器

  update Student set Sage = Sage + 1;
  

  

  delete from Student where Sno = '2017004';
  

  

  2.行级触发器

• 语法

  create or replace trigger 触发器名
  {before | after} on 表名
  [referencing OLD as old | New as new]
  --old : 旧数据
  --new ; 新数据
  for each row
    --for each row : 行级触发器
    [when 条件]
    --when 条件 ：当条件满足的时候，触发器才执行
  declare
    声明部分：
  begin
    执行部分：
  end;
  

例4 创建使用after 关键字的行级触发器

• 创建触发器

  create or replace trigger tri_Test4 after update on SC
  for each row
    begin
      dbms_output.put_line('创建成功！');
    end;
  

• 测试触发器

  update SC set Grade = Grade - 10;
  

  

• 测试前SC表中的数据
  

• 测试后SC表中的数据
  

例5 创建使用after关键字并带有旧数据和新数据的行级触发器

• 创建触发器

  create or replace trigger tri_Test4 after insert or delete or update of Grade on SC
  for each row
    begin
      if deleting then
        dbms_output.put_line('SC表中被删除的选课记录，学号：' ||  :old.Sno || ',课程号：' || :old.Cno || ',成绩：'||  :old.Grade);
      end if;
      if inserting then
        dbms_output.put_line('SC表中被插入的选课记录，学号：' ||  :new.Sno || ',课程号：' ||  :new.Cno || ',成绩：'||  :new.Grade);
      end if;
      if updating then
        dbms_output.put_line('更新前的选课记录，学号：' ||  :old.Sno || ',课程号：' ||  :old.Cno || ',成绩：'||  :old.Grade);
        dbms_output.put_line('更新后的选课记录，学号：' ||  :new.Sno || ',课程号：' ||  :new.Cno || ',成绩：'||  :new.Grade);
      end if;
    end;
  

• 测试触发器

  delete from SC where Sno = '2017002';
  

  

  update SC set Grade = Grade + 2 where Sno = '2017001';
  

  

  例6 创建使用before关键字的行级触发器，更新学生的年龄，要求更新后的年龄不能小于原来的年龄。

• 创建触发器

  create or replace trigger tri_Test5 before update of Sage on Student
  for each row
    begin
      if :old.Sage>:new.Sage then
        raise_application_error(-20001,'更新后的年龄小于原来的年龄');
      else 
        dbms_output.put_line('更新前的年龄：'|| :old.Sage);
        dbms_output.put_line('更新后的年龄：'|| :new.Sage);
      end if;
    end;
  

• 测试触发器

  update Student set Sage = Sage - 5 where Sno = '2017003';
  

  

  update Student set Sage = Sage + 5 where Sno = '2017003';