Oracle SQL触发器

一、触发器
　　触发器是一个数据库对象，是一个特殊的过程，当特定的时间发生时隐式地执行。比如在一个表中发生插入、更新或删除的时间，或者 CREATE、ALTER 这样的数据定义语句执行时，触发器会隐式执行。当一些用户行为或数据库系统行为发生时(例如用户登陆或数据库关闭时)，触发器也会隐式执行。
　　触发器组成部分：
　　　　触发时间　　　　触发器体执行的时机　　　　BEFORE、AFTER
　　　　触发事件　　　　那类具体的数据操纵语句　　INSERT、UPDATE、DELETE、CREATE、ALTER
　　　　触发器类型　　　触发器体执行次数　　　　　Statement、Row
　　　　触发器体　　　　触发器执行的具体操作　　　PL/SQL 块
【注意】：
当触发器类型为 Statement 时，称为语句触发器，触发器体对于触发事件只执行一次，及时没有行受到影响。
当类型为 Row 时，称为行触发器，触发器体对受出发时间影响的每行执行一次。

二、创建和测试语句触发器
1.语法形式

CREATE [OR REPLACE] TRIGGER tname
   timing
   event1[OR event2 OR event3]
   ON table
trigger_body

　　其中，tname 表示触发器名字，timing 表示出发时间，event1、event2、event3 表示触发事件，table 表示针对的表，trigger_body 表示触发器体。

2.语句触发器示例
　　例如需要创建这样一个触发器，只能在周一到周五的 9：00 到 18：00 才可以针对部门表 departments 进行 DML 操作。

create or replace trigger dml_depts_time 
   before                                --在触发事件发生前
   insert or update or delete      --触发事件为增删改
   on departments             --对表departments
begin                                                  --执行触发器体
   if to_char(sysdate,'HH24:MI') not between '08:00' and '18:00' 
      or to_char(sysdate,'DY') in ('SAT','SUN') 
   then 
      raise_application_error(-20205,'You may only make changes during normal office hours');
   end if;
end dml_depts_time

　　需要说明的是，上面创建触发器的 SQL 语句中， RAISE_APPLICATION_ERROR 是一个内建过程，它返回一个错误給用户，并导致 PL/SQL 块失败。当一个数据库触发器失败时，触发语句会自动回滚。
　　编译该触发器，通过 PL/SQL Dev 查看 Triggers 文件夹，可以看到刚编译的 dml_depts_time 触发器。为了验证触发器是否可以使用，选择在非工作时间，执行下面的 SQL 语句更新部门表的数据。

update departments
set department_name = 'IT GROUP'
where department_id = 60

结果

 

三、创建和测试行触发器
1.语法形式

 CREATE [OR REPLACE] TRIGGER tname
     timing
     event1 [OR event2 OR event3]
     ON table
     [REFERENCING OLD AS old|NEW AS new]
     FOR EACH ROW
     [WHEN(condition)]
trigger_body

2.行触发器示例
　　例如需要创建一个行触发器，当更改了雇员表中某行的职位编号字段或部门编号字段后，自动在职位变迁表 job_history 中增加一行记录，记录该雇员的职位(包括部门)变迁情况。创建行触发器的 SQL 语句如下：

create or replace trigger update_job_history
   after                                        -- 在触发事件发生后
   update of job_id,department_id         -- 触发事件是修改职位或部门
   on employees                    --对表employ
   for each row                                      -- 对每行
begin                                                       --执行触发器体
   insert into job_history(employee_id, start_date, end_date, job_id, department_id)
   values (:old.employee_id,:old.hire_date,sysdate,:old.job_id,:old.department_id);
end;

　　编译该触发器。为了验证该触发器是否起作用，执行下面的 SQL 语句（更新两行）并提交食物，打开职位变迁表 job_history , 自动增加了两行记录。

update employees
set department_id = 90
where employee_id in (106,107)

select * from hr.job_history

 四、小题
1.不允许删除编号为7369的员工；

create or replace trigger nodelete_emp
    before
    delete 
    on scott.emp
    for each row
begin
    if :old.empno = 7369 then -- NEW 或 OLD 引用，不允许在表级触发器中
       raise_application_error(-20006,'you can not delete the info .');
    end if ;
end ;

delete emp where empno = 7369;

结果

2.使用触发器实现主键自增长；

create sequence seq  -- 创建序列
      increment by 1 -- 步长为1
      start with 20  -- 初始值为 20
      nomaxvalue     -- 无最大值
      nocycle        -- 不循环、

CREATE OR REPLACE TRIGGER add_auto_pk --创建自动增长 触发器
  BEFORE 
  INSERT ON scott.emp
  FOR EACH ROW
DECLARE
  v_emp_no NUMBER(4);
BEGIN
  SELECT seq3.nextval INTO v_emp_no FROM dual; 
  :new.empno := v_emp_no;
END;