使用ORACLE触发器- HITANGJUN BLOG企业库|免费b2b网站

一使用场景
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语句不能进行下列操作:
读或修改触发语句的任何变异表,包括触发表本身
读或修改触发表的约束表中的主键,外键,{wy}列

–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不需要使用冒号。

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

欢迎补充。

Tagged as: ,