oracle的行级触发器使用

行级触发器:

当触发器被触发时,要使用被插入、更新或删除的记录中的列值,有时要使用操作前、后列的值.

:NEW 修饰符访问操作完成后列的值

:OLD 修饰符访问操作完成前列的值

例1: 建立一个触发器, 当职工表 emp 表被删除一条记录时,把被删除记录写到职工表删除日志表中去。

CREATE   TABLE  emp_his  AS   SELECT   *   FROM  EMP  WHERE   1 = 2 ; 

CREATE   OR   REPLACE   TRIGGER  tr_del_emp 
   BEFORE  DELETE   -- 指定触发时机为删除操作前触发
    ON  scott.emp 
    FOR  EACH ROW    -- 说明创建的是行级触发器 
BEGIN
    -- 将修改前数据插入到日志记录表 del_emp ,以供监督使用。
    INSERT   INTO  emp_his(deptno , empno, ename , job ,mgr , sal , comm , hiredate )
        VALUES ( :old.deptno, :old.empno, :old.ename , :old.job,:old.mgr, :old.sal, :old.comm, :old.hiredate );
END ;
DELETE  emp  WHERE  empno = 7788 ;
DROP   TABLE  emp_his;
DROP   TRIGGER  del_emp;

例2:限制对Departments表修改(包括INSERT,DELETE,UPDATE)的时间范围,即不允许在非工作时间修改departments表。

CREATE   OR   REPLACE   TRIGGER  tr_dept_time
BEFORE  INSERT   OR   DELETE   OR   UPDATE  
ON  departments
BEGIN
  IF  (TO_CHAR(sysdate, ' DAY ' )  IN  ( ' 星期六 ' ,  ' 星期日 ' ))  OR  (TO_CHAR(sysdate,  ' HH24:MI ' )  NOT   BETWEEN   ' 08:30 '   AND   ' 18:00 ' )  THEN
     RAISE_APPLICATION_ERROR( - 20001 ,  ' 不是上班时间,不能修改departments表 ' );
  END   IF ;
END ;

例3:限定只对部门号为80的记录进行行触发器操作。

CREATE   OR   REPLACE   TRIGGER  tr_emp_sal_comm
BEFORE  UPDATE   OF  salary, commission_pct
        OR   DELETE
ON  HR.employees
FOR  EACH ROW
WHEN  (old.department_id  =   80 )
BEGIN
  CASE
      WHEN  UPDATING ( ' salary ' )  THEN
         IF  :NEW.salary  <  :old.salary  THEN

           RAISE_APPLICATION_ERROR( - 20001 ,  ' 部门80的人员的工资不能降 ' );
         END   IF ;
      WHEN  UPDATING ( ' commission_pct ' )  THEN

         IF  :NEW.commission_pct  <  :old.commission_pct  THEN
           RAISE_APPLICATION_ERROR( - 20002 ,  ' 部门80的人员的奖金不能降 ' );
         END   IF ;
      WHEN  DELETING  THEN
          RAISE_APPLICATION_ERROR( - 20003 ,  ' 不能删除部门80的人员记录 ' );
      END   CASE ;
END ; 

/*  实例:  UPDATE employees SET salary = 8000 WHERE employee_id = 177;  DELETE FROM employees WHERE employee_id in (177,170);  */

例4: 利用行触发器实现级联更新。在修改了主表 regions 中的 region_id 之后( AFTER ),级联的、自动的更新子表 countries 表中原来在该地区的国家的 region_id 。

CREATE   OR   REPLACE   TRIGGER  tr_reg_cou
AFTER  update   OF  region_id
ON  regions
FOR  EACH ROW
BEGIN
 DBMS_OUTPUT.PUT_LINE( ' 旧的region_id值是 ' || :old.region_id
                   || ' 、新的region_id值是 ' || :new.region_id);
  UPDATE  countries  SET  region_id  =  :new.region_id
  WHERE  region_id  =  :old.region_id;
END ;

例5:在触发器中调用过程。

CREATE   OR   REPLACE   PROCEDURE  add_job_history
 ( p_emp_id          job_history.employee_id % type
   , p_start_date      job_history.start_date % type
  , p_end_date        job_history.end_date % type
   , p_job_id          job_history.job_id % type
   , p_department_id   job_history.department_id % type
   )
IS
BEGIN
  INSERT   INTO  job_history (employee_id, start_date, end_date,
                           job_id, department_id)
   VALUES (p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
END  add_job_history;

-- 创建触发器调用存储过程...
CREATE   OR   REPLACE   TRIGGER  update_job_history
 AFTER  UPDATE   OF  job_id, department_id  ON  employees
  FOR  EACH ROW
BEGIN
 add_job_history(:old.employee_id, :old.hire_date, sysdate,
                  :old.job_id, :old.department_id);
END ; 

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数据和云

运维技巧 - 活用临时表隔离冷热数据

编辑手记:Oracle给了我们很多工具,在日常数据库管理中活用这些工具方可发挥最大效能。 作者简介: 张洪涛 富士康 DBA 在数据库监控过程中发现考勤数据...

41450
来自专栏我的博客

Sqlite创建表以及默认当前时间

1、创建数据库 CREATE TABLE Joyous_sqlite ( id INTEGER NOT NULL, create_time TIMESTAM...

35130
来自专栏MYSQL轻松学

MYSQL常用SQL汇总

1、查看当前应用连接,连接数突增排查 select user,SUBSTRING_INDEX(host,':',1) as ip , count(*) as c...

39960
来自专栏jeremy的技术点滴

保存mysql InnoDB的auto_increment值另类方案

28650
来自专栏数据库新发现

CTAS、Nologging以及数据库运行模式

http://www.itpub.net/showthread.php?threadid=242761

13440
来自专栏www.96php.cn

mysql 快速生成百万条测试数据

1、生成思路 利用mysql内存表插入速度快的特点,先利用函数和存储过程在内存表中生成数据,然后再从内存表插入普通表中 2、创建内存表及普通表 CREATE T...

684120
来自专栏码农二狗

mysql无符号整型溢出

13740
来自专栏PHP在线

MySQL DELETE语句和TRUNCATE TABLE语句的区别

MySQL DELETE语句和TRUNCATE TABLE语句功能相似,但是二者究竟有何区别呢?下文就将为您分析MySQL DELETE语句和TRUNCATE ...

394140
来自专栏撸码那些事

MySQL——索引优化实战

上篇文章中介绍了索引的基本内容,这篇文章我们继续介绍索引优化实战。在介绍索引优化实战之前,首先要介绍两个与索引相关的重要概念,这两个概念对于索引优化至关重要。

9740
来自专栏互联网开发者交流社区

SQL触发器实例(上)

17040

扫码关注云+社区

领取腾讯云代金券