架构师

您现在的位置是:首页 > 技术博客 > 数据库

数据库

Oracle如何使用触发器?

架构师小跟班 2019-08-28数据库
创建触发器语法:create [or replace] trigger trigger_name{before | after }{insert | delete | update [of column [, column …]]}[or {insert | delete | update [of col

创建触发器语法:

create [or replace] trigger trigger_name

{before | after }

{insert | delete | update [of column [, column …]]}

[or {insert | delete | update [of column [, column …]]}...]

on [schema.]table_name | [schema.]view_name 

[referencing {old [as] old | new [as] new| parent as parent}]

[for each row ]

[when condition]

pl/sql_block | call procedure_name;

说明:

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

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

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

2、for each row选项说明触发器为行触发器。

3、行触发器和语句触发器的区别:

行触发器要求当一个dml语句操走影响数据库中的多行数据时,对于其中的每个数据行,只要它们符合触发约束条件,均激活一次触发器;而语句触发器将整个语句操作作为触发事件,当它符合约束条件时,激活一次触发器。当省略for each row 选项时,before 和after 触发器为语句触发器,而instead of 触发器则只能为行触发器。

4、referencing 子句说明相关名称,在行触发器的pl/sql块和when 子句中可以使用相关名称参照当前的新、旧列值,默认的相关名称分别为old和new。

触发器的pl/sql块中应用相关名称时,必须在它们之前加冒号(:),但在when子句中则不能加冒号。

5、when 子句说明触发约束条件。

condition 为一个逻辑表达时,其中必须包含相关名称,而不能包含查询语句,也不能调用pl/sql 函数。when 子句指定的触发约束条件只能用在before 和after 行触发器中,不能用在instead of 行触发器和其它类型的触发器中。

6、当一个基表被修改( insert, update, delete)时要执行的存储过程,执行时根据其所依附的基表改动而自动触发,因此与应用程序无关,用数据库触发器可以保证数据的一致性和完整性。

编写触发器注意事项:

1、触发器不接受参数。

2、一个表上最多可有12个触发器,但同一时间、同一事件、同一类型的触发器只能有一个。并各触发器之间不能有矛盾。

3、在一个表上的触发器越多,对在该表上的dml操作的性能影响就越大。

4、触发器最大为32kb。若确实需要,可以先建立过程,然后在触发器中用call语句进行调用。

5、在触发器的执行部分只能用dml语句(select、insert、update、delete),不能使用ddl语句(create、alter、drop)。

6、触发器中不能包含事务控制语句(commit,rollback,savepoint)。因为触发器是触发语句的一部分,触发语句被提交、回退时,触发器也被提交、回退了。

7、 在触发器主体中调用的任何过程、函数,都不能使用事务控制语句。

8、 在触发器主体中不能申明任何long和blob变量。新值new和旧值old也不能向表中的任何long和blob列。

9、不同类型的触发器(如dml触发器、instead of触发器、系统触发器)的语法格式和作用有较大区别。

简单示例

例1:删除部门表时,将该记录插入日志表中

新建sql窗口,输入以下语句,执行,会在triggers目录下生成触发器:

create or replace trigger tmp_dept_dml_log
  before delete  --指定触发时机为删除操作前触发
  on tmp_dept   --操作的表
  for each row  --说明创建的是行级触发器
begin
  --将删除前的记录放入日志表tmp_dept_dml_log
  insert into tmp_dept_dml_log (dept_id, dept_no, dept_name) values (:old.dept_id, :old.dept_no, :old.dept_name);
end;
执行删除操作,可发现,日志表中多了一条记录。
delete from tmp_dept where dept_id=10033;

例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;

 例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;


文章评论