使用ORACLE触发器

来源：互联网发布：手机数控编程软件编辑：程序博客网时间：2024/05/01 22:08

一使用场景

Oracle触发器，见名知意，就是当条件吻合时，被动触发一系列的DB操作。触发器是由Oracle数据库本身被动触发的动作，与存储过程不同的是，存储过程可以通过外部code或触发器等显示的调用，而触发器是数据库本身的被动行为。例如，当你需要在save A表记录的时候，同时向B表记录save A表的log信息，这样，触发器就很符合您的要求了。

二环境和备用命令

环境：本篇内容基于CENTOS和oracle 10g调试。

备用命令：

停止oracle服务

启动oracle

其他命令和相关设置：

三语法介绍

触发器语法

CREATE [OR REPLACE] TRIGGER trigger_name --创建或替换一个触发器，trigger_name指定触发器名称

{BEFORE | AFTER | INSTEAD OF} --指定何时执行触发器

{INSERT | DELETE | UPDATE [OF column [, column …]]} --指定触发变异表的动作

ON {[schema.] table_name | [schema.] view_name} -- 指定被影响的表、schema或视图

[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]

--old修饰访问操作完成前列的值﹐new修饰访问操作完成后列的值

[FOR EACH ROW ] --指定行级触发或表达式触发

[WHEN condition] --指定触发条件

Declare --申明变量

-- 变量;

Begin

--设定触发后执行的代码段

-- trigger_body;

End ;

用途﹕

–1﹐强制执行 business rule.

–2﹐更新其他 table 中的数据.

–3﹐标示数据列以供处理.

–4﹐以信号通知已发生事件

四注意要点

–1﹐不能使用rollback, commit ,savepoints,transaction交易控制，请具体验证

–2﹐不能使用long , long raw 变量

–3,after 的方式比 before 有效率

–4,行级触发器不可对 trigger 上的本表select,insert,update,delete操作

–5,:new,:old,:parent关键词只可以用在行级触发器中[for each row]

–6,:new,:old,关键词前的冒号只能在触发器体内有效.即在触发器体外不需要冒号,例如在when子句中

–7,:new,:old,伪记录,但是定义的 rowtype 类型的变量不能接受:new,或:old,也不能将:new或:old传递到接受rowtype类型的

–8, 代码大小必须小于32KB

old与new详解：

old修饰访问操作完成前列的值﹐new修饰访问操作完成后列的值

利用 new 与 old 来参考数据域的值

:OLD :NEW

delete 被删除的值 NULL

insert NULL 被建立的值

update 旧值更改后的新值

在 begin 中使用 , 须加冒号 :

在 when 与 referencing 不须加冒号 :

过程或函数中

–8,触发器数据字典:user_triggers

–9,变异表(mutating table) 和约束表(constrainting table)变异表是当前被DML语句修改的表,对触发器来说变异表就是在其上定义的表.由于执行delete cascade引用完整性约束更新的表也是变异表，约束表是一种需要实施引用完整性约束而读入的表.

–10,触发器中的SQL语句不能进行下列操作:

读或修改触发语句的任何变异表,包括触发表本身

读或修改触发表的约束表中的主键,外键,唯一列

–11,由于在行级触发器中不能在触发表上使用select,update,delete,update语句,但可以使用语句级触发器,但是如果要使用:new,:old关键词,就必须使用行级触发器,为了既可以使用:new,:old关键词,有可以在触发表上使用select, update, delete, update语句我们可以在一个表上定义两个触发器:一个行级触发器,一个语句级触发器,在行级触发器中，使用:new, :old关键词,在语句级中使用select,update,insert,delete语句，至于如何记录:new的值,我们可以使用package,并在package 中使用table数据类型。

–12,行级触发器和语句级触发器的触发先后顺序:语句级别前,行级级别前,行级级别后,语句级别后。在REFERENCING子句中new前不需要加冒号,该子句需要放在 for each row 之前。

–13,有 for eache row 关键词的为行级触发器，否则是语句级触发器

–选择性启动 TRIGGER﹐使用关键词deleting,updating,inserting

–如果是因为delete而执行trigger﹐则返回true

–如果是因为update而执行trigger﹐则返回true

–如果是因为insert而执行trigger﹐则返回true

五实战实例

☆创建一个触发器：

上面的名为demo_trigger的触发器定义了当更新USER表记录完成的时候向user_info表插入一条记录。

☆指定触发前执行：

将前例中的AFTER关键字换成BEFORE即可。如果设定为AFTER则是在完成后，见上一个例子。

☆指定触发动作：

这里指定了触发的动作为更新user表成功后，你也可以指定为插入或更新或删除时执行触发器

☆使用:new和:old来引用变异表的记录

CREATE OR REPLACE TRIGGER demo_trigger

AFTER UPDATE ON "user"

REFERENCING OLD AS "USER_BEFORE_UPDATED" NEW AS "USER_AFTER_UPDATED"

FOR EACH ROW

DECLARE

BEGIN

INSERT INTO user_info

VALUES

(sysdate,"update a record into user table.",

:USER_BEFORE_UPDATED.userstatus,

:USER_AFTER_UPDATED.userstatus );

END;

这里使用REFERENCING OLD AS “USER_BEFORE_UPDATED” NEW AS “USER_AFTER_UPDATED” 来记录更新前和更新后USER表的记录，并指定别名，在代码块中可以使用这些别名来引用记录值，如 :USER_BEFORE_UPDATED.userstatus 。

REFERENCING 子句必须放在 FOR EACH ROW子句之前。

☆指定触发条件

CREATE OR REPLACE TRIGGER demo_trigger

AFTER UPDATE ON "user"

REFERENCING OLD AS "USER_BEFORE_UPDATED" NEW AS "USER_AFTER_UPDATED"

FOR EACH ROW

WHEN(NEW.age >= 60 OR old.age >= 60)

DECLARE

BEGIN

INSERT INTO user_info

VALUES

(sysdate,"update a record into user table.",

:USER_BEFORE_UPDATED.userstatus,

:USER_AFTER_UPDATED.userstatus );

END;

上例使用WHEN(NEW.age >= 60 OR old.age >= 60) 限定只有当新记录或老记录的age>=60时，才执行触发器指定的代码段。

☆声明变量

CREATE OR REPLACE TRIGGER demo_trigger

AFTER UPDATE ON "user"

REFERENCING OLD AS "USER_BEFORE_UPDATED" NEW AS "USER_AFTER_UPDATED"

FOR EACH ROW

WHEN(NEW.age >= 60 OR old.age >= 60)

DECLARE

olduserstatus varchar2(20);

newuserstatus varchar2(20);

dtime number;

BEGIN

INSERT INTO user_info

VALUES

(sysdate,"update a record into user table.",

:USER_BEFORE_UPDATED.userstatus,

:USER_AFTER_UPDATED.userstatus );

END;

上例通过DECLARE 声明了olduserstatus, newuserstatus, dtime 三个变量，您还可以在代码段中使用他们，如下：

CREATE OR REPLACE TRIGGER demo_trigger

AFTER UPDATE ON "user"

REFERENCING OLD AS "USER_BEFORE_UPDATED" NEW AS "USER_AFTER_UPDATED"

FOR EACH ROW

WHEN(NEW.age >= 60 OR old.age >= 60)

DECLARE

olduserstatus varchar2(20);

newuserstatus varchar2(20);

dtime number;

BEGIN

olduserstatus := 'olduserstatus ';

--将当前系统时间计算成数值型并赋值给dtime

SELECT (sysdate - 8 / 24 - to_date('1970-01-01', 'yyyy-mm-dd')) * 86400000

INTO dtime from dual;

INSERT INTO user_info

VALUES

(dtime ,"update a record into user table.",

olduserstatus ,

:USER_AFTER_UPDATED.userstatus );

END;

☆编写触发后执行的代码段

CREATE OR REPLACE TRIGGER demo_trigger

AFTER INSERT OR UPDATE ON "user"

REFERENCING OLD AS "USER_BEFORE_UPDATED" NEW AS "USER_AFTER_UPDATED"

FOR EACH ROW

WHEN(NEW.age >= 60 OR old.age >= 60)

DECLARE

olduserstatus varchar2(20);

newuserstatus varchar2(20);

dtime number;

BEGIN

olduserstatus := 'olduserstatus ';

--将当前系统时间计算成数值型并赋值给dtime

SELECT (sysdate - 8 / 24 - to_date('1970-01-01', 'yyyy-mm-dd')) * 86400000

INTO dtime from dual;

IF updating THEN

INSERT INTO user_info

VALUES

(dtime ,"update a record into user table.",

olduserstatus ,

:USER_AFTER_UPDATED.userstatus );

ELSIF inserting THEN

INSERT INTO user_info

VALUES

(dtime ,"INSERT a record into user table.",

olduserstatus ,

:USER_AFTER_UPDATED.userstatus );

END IF;

END;

上面通过在”BEGIN END”代码段中使用IF判断当前动作是update 或 insert 而执行不同的代码段。关于触发动作返回值具体可参考注意要点第13条。

六特别提醒

计算时间

--将当前系统时间计算成数值型并赋值给dtime

--current_timestamp(3) 表示截取3位有效位

select ((SYSTIMESTAMP - 8 / 24 - to_date('1970-01-01', 'yyyy-mm-dd')) * 86400000 )

+ (to_char(current_timestamp(3),'FF') ) from dual;

通过sql为变量赋值：使用SELECT INTO 变量名来进行赋值。

何时使用冒号来使用变量：

对于在REFERENCING 后为old 和 new定义的别名，在代码块中需要使用冒号:来引用；对于DECLARE 中定义的变量在代码块中则不需要使用冒号；

在WHEN子句中使用NEW 和 OLD不需要使用冒号。

为变量赋值须使用冒号等于号 := 来赋值。