Oracle--trigger

开发者博客：www.developsearch.com

 

 

触发器在数据库里以独立的对象存储，它与存储过程和函数不同的是，存储过程与函数需要用户显示调用才执行，而触发器是由一个事件来启动运行。即触发器是当某个事件发生时自动地隐式运行。并且，触发器不能接收参数。ORACLE事件指的是对数据库的表进行的INSERT、UPDATE及DELETE操作或对视图进行类似的操作。ORACLE将触发器的功能扩展到了触 发ORACLE，如数据库的启动与关闭等。所以触发器常用来完成由数据库的完整性约束难以完成的复杂业务规则的约束，或用来监视对数据库的各种操作，实现 审计的功能。

 

参考文章 ：http://blog.csdn.net/indexman/article/details/8023740

 

编写触发器时，需要注意以下几点：
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触发器、系统触发器)的语法格式和作用有较大区别。

 

创建触发器的一般语法是:

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;

 

查看某个表的触发器

select   *   from   all_triggers
where   table_name   = upper( 'tbname ')

 

删除触发器：
DROP TRIGGER trigger_name;

 

禁用或启用触发器
ALTER TIGGER trigger_name [DISABLE | ENABLE ];

 

:old  与 :new 的区别：
顾名思义，new是新插入的数据，old是原来的数据
insert只会有new，代表着要插入的新记录
delete只会有old，代表着要删除的记录
update由于执行的是先删除旧的记录，再插入新的记录，因此new和old都会有，且含义与上面的相同

这两个变量只有在使用了关键字 "FOR EACH ROW"时才存在

 

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

 

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

 

创建替代(INSTEAD OF)触发器INSTEAD_OF 用于对视图的DML触发，由于视图有可能是由多个表进行联结(join)而成，因而并非是所有的联结都是可更新的。但可以按照所需的方式执行更新。

创建INSTEAD OF触发器需要注意以下几点：
1、只能被创建在视图上，并且该视图没有指定WITH CHECK OPTION选项。
2、不能指定BEFORE 或 AFTER选项。
3、FOR EACH ROW子可是可选的，即INSTEAD OF触发器只能在行级上触发、或只能是行级触发器，没有必要指定。
4、没有必要在针对一个表的视图上创建INSTEAD OF触发器，只要创建DML触发器就可以了。

例1：

CREATE OR REPLACE VIEW emp_view AS
SELECT deptno, count(*) total_employeer, sum(sal) total_salary
FROM emp GROUP BY deptno;

在此视图中直接删除是非法：
SQL>DELETE FROM emp_view WHERE deptno=10;
DELETE FROM emp_view WHERE deptno=10

ERROR 位于第 1 行:
ORA-01732: 此视图的数据操纵操作非法

但是我们可以创建INSTEAD_OF触发器来为 DELETE 操作执行所需的处理，即删除EMP表中所有基准行：
CREATE OR REPLACE TRIGGER emp_view_delete
   INSTEAD OF DELETE ON emp_view FOR EACH ROW
BEGIN
   DELETE FROM emp WHERE deptno= :old.deptno;
END emp_view_delete;
DELETE FROM emp_view WHERE deptno=10;
DROP TRIGGER emp_view_delete;
DROP VIEW emp_view; 

 

例2：创建复杂视图，针对INSERT操作创建INSTEAD OF触发器，向复杂视图插入数据。

创建视图：
CREATE OR REPLACE FORCE VIEW "HR"."V_REG_COU" ("R_ID", "R_NAME", "C_ID", "C_NAME")
AS
 SELECT r.region_id,
    r.region_name,
    c.country_id,
    c.country_name
 FROM regions r,
    countries c
 WHERE r.region_id = c.region_id;

创建触发器：
CREATE OR REPLACE TRIGGER "HR"."TR_I_O_REG_COU" INSTEAD OF
 INSERT ON v_reg_cou FOR EACH ROW DECLARE v_count NUMBER;
BEGIN
 SELECT COUNT(*) INTO v_count FROM regions WHERE region_id = :new.r_id;
 IF v_count = 0 THEN
    INSERT INTO regions
      (region_id, region_name
      ) VALUES
      (:new.r_id, :new.r_name
      );
 END IF;

 SELECT COUNT(*) INTO v_count FROM countries WHERE country_id = :new.c_id;
 IF v_count = 0 THEN
    INSERT
    INTO countries
      (
        country_id,
        country_name,
        region_id
      )
      VALUES
      (
        :new.c_id,
        :new.c_name,
        :new.r_id
      );
 END IF;
END;

 

 

简介：触发器是一种特殊类型的存储过程，它在指定的表中的数据发生变化时自动生效，触发器被调用时自动执行INSERT、UPDATE、DELETE语句和SELECT语句，实现表间的数据完整性和复杂的业务规则。

 

分类：根据服务器或数据库中调用触发器的操作不同，SQL Server 的触发器分为DML触发器和DDL触发器。

（1）DML 触发器是当数据库服务器中发生数据操作语言（DML）事件时要执行的操作（作用于表）。

（2）DDL触发器是 SQL Server 的新增功能（作用于数据库或服务器）。

 

创建DML基本语句格式如下：

CREATE  TRIGGER 触发器名

ON  表 ｜ 视图

FOR｜AFTER｜INSTEAD OF  

INSERT｜UPDATE｜DELETE

AS

DML语句

 

创建DDL基本语句格式如下：

CREATE  TRIGGER 触发器名

ON  服务器 | 数据库

FOR｜AFTER  

AS

DDL语句

 

禁用/启用和删除触发器：

针对某个表创建的触发器，可以根据需要，禁止或启用其执行（触发器创建时默认为启用状态）。

其语法格式为：

    ALTER TABLE 表名

    {ENABLE |DISABLE } TRIGGER 触发器名称

其中：

     ENABLE：该选项为启用触发器

     DISABLE：该选项为禁用触发器

 

删除触发器：DROP TRIGGER { 触发器名称 }

 

 

-- 创建触发器 : 表（SY_NOTE_SALES_INFO）执行insert时根据sequence自动生成ID

CREATEORREPLACETRIGGER"ECOLOGY".SY_NOTE_SALES_INFO_TriggerbeforeinsertonSY_NOTE_SALES_INFOfor

eachrowbeginselectSY_NOTE_SALES_INFO_ID.nextvalinto :new.idfromdual; end;

 

 

实例1：在WebShop数据库中创建一个触发器，实现在用户信息表（Users）中删除用户信息时，显示“×××用户已被删除！”。

Create trigger tr_delete

On users

For delete

As

Begin

Declare @user varchar(30)

Select @user=u_name from deleted

Print @user +’用户已被删除’

End

Delete from users where u_name=‘trigger’

 

 

实例2：在WebShop数据库中创建一个触发器，实现在生成订单时，即往Orders表中插入订单记录时进行如下检查。如果插入的订单中的商品的支付编号p_ID不存在或者下达订单的会员号c_ID不存在，必须取消订单插入操作，并返回一条错误消息。

Create trigger tr_placeorder

On orders

For insert,update

As

Begin

Declare @p_no char(6)

Declare @c_no char(5)

Select @p_no=payment.p_id

From payments,inserted

Where payment.p_id=inserted.p_id

Select @c_no=customers.c_id

From customers,inserted

Where customers.c_id=inserted.c_id

If @p_no is null or @c_no is null

Begin

Rollabck transaction

Raiserror(‘不存在这样的支付方式或会员’，16，10)

End

end

 

 

 

实例3----增 删 改

//触发器名UserToTemp

create or replace trigger UserToTemp after insert or update or delete

on user_info for each row//对表user_info操作时触发以下事件

declare//声明变量

    integrity_error exception;

    errno            integer;

    errmsg           char(200);

    dummy            integer;

    found            boolean;

    sexy              varchar2(20);

 

begin

if inserting then

    select sex into sexy from user_info_test;//取出user_info_test表中的sex字段的值赋值给变量sexy

    insert into User_info_temp(ID,UserName,PassWord,Createdate,Status) values(:NEW.ID,:NEW.UserName,sexy,:NEW.createdate,:NEW.status);//:NEW.UserName的值为表user_info新增加的数据

elsif updating then

    update User_info_temp set ID=:NEW.ID,UserName=:NEW.UserName,PassWord=:NEW.PassWord,Status=:NEW.Status where id=:OLD.id;

elsif deleting then

    delete from User_info_temp where id=:OLD.id;

end if;

exception

    when integrity_error then

       raise_application_error(errno, errmsg);

end;

 

开发者博客：www.developsearch.com