oracle中的触发器,oracle的trigger

oracle中的触发器,oracle的trigger,Oracle触发器trigger详解

本文主要详细介绍了Oracle触发器,有一定的参考价值,感兴趣的朋友可以参考一下。

触发器的相关概念和语法供您参考。具体如下。

概述

这篇博文主要讨论了以下内容:

什么是触发器?

触发器的应用场景

触发器的语法

触发器的类型。

情况

数据:

触发器的概念和第一个触发器

数据库触发器是与表相关联的存储PL/SQL语句。

每当对指定的表发出特定的数据操作语句(insert update delete)时,Oracle都会自动执行触发器中定义的语句序列。

举个简单的例子:

当一条新记录添加到员工表中时,将自动打印“新员工成功插入”。

创建或替换触发器插入StaffHint

在xgj_test上插入后

对于每一行

声明

-这里是局部变量

开始

Dbms_output.put_line(新员工成功);

end insertStaffHint

触发器的应用场景

复杂的安全检查

数据确认

数据库审计

数据的备份和审计

触发器的语法

创建[或替换]触发器触发器名称

{之前|之后}

{插入|删除|更新[第[,列…]]}

[或{插入|删除|更新[第[,列…]]].]

在[架构上。]table_name | [schema。]视图名称

[引用{ OLD[AS]OLD | NEW[AS]NEW | PARENT AS PARENT }]

[每一行]

[条件时]

PL/SQL _ BLOCK | CALL procedure _ name;

其中包括:

BEFORE

AFTER

分别表示触发器的触发顺序是预触发和后触发。前触发器在触发器事件执行前触发当前创建的触发器,后触发器在触发器事件执行后触发当前创建的触发器。

FOR EACH ROW

选项表示触发器是行触发器。

行触发器和语句触发器的区别如下:当一条DML语句遍历数据库中的多行数据时,只要满足触发器约束,每个数据行都要激活一次触发器;语句触发器将整个语句操作作为一个触发事件,当满足约束条件时,触发器被激活一次。

当省略FOR EACH ROW选项时,BEFORE和AFTER触发器是语句触发器,而INSTEAD OF触发器只能是行触发器。

REFERENCING

子句描述了相关的名称,可以在PL/SQL块和行触发器的WHEN子句中使用,以引用当前的新列值和旧列值。默认的相关名称分别是旧名称和新名称。在触发器的PL/SQL块中应用相关名称时,必须在它们前面添加冒号(:),但不能在WHEN子句中添加冒号。

WHEN

条款描述了触发约束。当条件是逻辑表达式时,它必须包含相关名称,但不能包含查询语句或PL/SQL函数。WHEN子句指定的触发器约束只能用于BEFORE和AFTER行触发器,而不能用于INSTEAD OF行触发器和其他类型的触发器。

当一个基表被修改(插入、更新、删除)时,存储过程会根据它所附加的基表的变化而自动触发,所以与应用程序无关。使用数据库触发器可以确保数据的一致性和完整性。

每个表最多可以创建12种类型的触发器:

插入前

在每行插入之前

插入后

每行插入后

更新前

在每行更新之前

更新后

每行更新后

删除前

删除每一行之前

删除后

删除每一行后

触发器的类型

Row trigger要求当一个DML语句操作影响数据库中的多行数据时,只要满足触发器约束条件,就为每个数据行激活一次触发器;在行级触发器中,使用:old和:new伪记录变量来标识值的状态。

触发器将整个语句操作作为一个触发事件,当满足约束条件时,触发器被激活一次。

当省略FOR EACH ROW选项时,BEFORE和AFTER触发器是语句触发器。

INSTEAD OF触发器只能是行触发器。

其他规则

触发器名称不同于过程名称和包名称。它是一个独立的名称空间,因此触发器名称可以与表或过程同名,但在模式中不能相同。

DML触发器的限制:

CREATE TRIGGER语句文本的字符长度不能超过32KB;

触发器主体中的SELECT语句只能是SELECT … INTO …结构或用于定义游标的SELECT语句。

在触发器中不能使用数据库事务控制语句COMMITROLLBACK,SVAEPOINT语句;

触发器调用的过程或函数不能使用数据库事务控制语句;

不能在触发器中使用LONG、LONG RAW类型。

触发器中可以引用LOB列的列值,但LOB列中的数据不能被修改:NEW

DML触发器的要点:

触发时机:

指定触发器的触发时间。如果指定了BEFORE,则表示在执行DML操作之前触发,以防止一些错误操作或者实现一些业务规则;如果指定为AFTER,则表示在DML操作执行后触发,以便记录操作或做一些后期处理。

触发事件:

导致触发器被触发的事件,即DML操作(插入、更新、删除)。可以是单个触发事件,也可以是多个触发事件的组合(只能用OR逻辑组合,不能用逻辑组合)。

条件谓词:

当触发器包含多个触发事件(插入、更新、删除)的组合时,需要使用ORACLE提供的以下条件谓词,以便分别处理不同的事件。

1).插入:当触发事件为插入时,值为真,否则为假。

2).Updating [(column _ 1,column _ 2,…,column_x)]:当触发事件为UPDATE时,如果修改了column_x,则值为TRUE,否则为FALSE。其中column_x是可选的。

3).删除:当触发事件为删除时,值为真,否则为假。

解发对象:

指定在哪个表和视图上创建触发器。

触发类型:

是语句级触发器还是行级触发器?

触发条件:

逻辑表达式由WHEN子句指定。在行级触发器上只能指定触发器条件,并且可以指定更新后的列列表。

触发器应用

触发器应用复杂的安全检查。

禁止在非工作时间插入数据。

/**

非工作时间(地球上的周六和周日,不是9: 00到18: 00之间)

禁止写入数据

首先要明确:触发器的类型——语句级触发器。

无论插入多少条数据,都没有必要检查每一行数据。只要不在这个时间段内,就不允许插入。

*/

创建或替换触发器addStafffCheck

在xgj_test上插入之前

声明

-这里是局部变量

开始

(星期六,星期日)中的To _ char (sysdate, day )或

to_number(to_char(sysdate, hh24 ))不在9和18之间

-禁止插入。

Raise_application_error(-20001,“非工作时间禁止数据插入”);

结束if;

end addStafffCheck

Raise_application_error用于定制plsql使用程序中的错误消息。

这个异常只能在数据库端的子程序(进程、函数、包、触发器)中使用,而不能在客户端的匿名块和子程序中使用。

语法为raise _ application _ error(error _ number,message [,[true false]]);其中error_number用于定义不正确的数字,

不正确的数字必须是介于-20000和-20999之间的负整数;Message用于指定不正确的消息,消息长度不能超过2048字节;

触发器应用二

数据确认

工资不能上下浮动。

分析:首先确认下一个触发器的类型,这是一个行级触发器,因为每一条数据都需要确认。对于每行都是必需的。

/**

加薪后的薪资不能低于加薪前的薪资。

1 :old和:new表示相同的记录。

2 :old代表这条线在被操作之前的值。

:new表示该行操作后该行的值。

*/

创建或替换触发器支票薪金

更新前

关于xgj_test

对于每一行

声明

-这里是局部变量。如果没有变量声明,declare可以省略。

开始

-如果是增加后的工资,那么增加前的工资怎么表达?

如果:新的。萨尔:旧的。萨尔那么

引发应用程序错误(-20002,涨后的薪水: || :new.sal || 小于涨前的薪水: | |:旧。sal);

结束如果;

结束检查薪金;

触发器应用三

数据库审计

创建基于值的触发器

创建表xgj _ record(info varchar 2(256));

创建或替换触发器添加记录

更新后

关于xgj_test

对于每一行

声明

-这里是局部变量

开始

如果:new.sal 6000那么

插入到xgj_record值(:新的。sal | | - | |:new。用户名| | - | |:新。job);

结束如果;

end addrecord

触发器应用四

数据的备份和同步

当给员工涨完工资后,自动备份到备份表中

将表xgj _测试_bak创建为select * from xgj _ test

创建或替换触发器数据备份

更新后

关于xgj_test

对于每一行

开始

更新xgj_test_bak set sal=:new.sal其中用户名=:新的。用户名;

结束数据返回;

神谕中还有个利用快照备份,是异步的。而利用触发器,是同步的。

其他

建立一个触发器,当职工表电磁脉冲表被删除一条记录时,把被删除记录写到职工表删除日志表中去

将表雇员信息系统创建为SELECT * FROM EMP,其中1=2;

创建或替换触发器tr_del_emp

删除前-指定触发时机为删除操作前触发

在斯科特. emp上

对于每一行-说明创建的是行级触发器

开始

-将修改前数据插入到日志记录表del_emp,以供监督使用。

插入emp_his(部门、员工、姓名、职务、经理、销售、通信、雇佣日期)

值例如old . deptno:old . empno:old . ename:old . job:old . mgr:old . sal:old . comm:old。hiredate);

结束;

删除empno=7788的电磁脉冲

删除表雇员信息系统

滴触发器德尔_ emp

限制对部门表修改(包括插入、删除、更新)的时间范围,即不允许在非工作时间修改部门表。

创建或替换触发器时间

在插入或删除或更新之前

关于部门

开始

中的IF (TO_CHAR(sysdate, DAY )星期六, 星期日))或(TO_CHAR(sysdate, HH24:MI )不在 08:30 和 18:00 之间),则

引发应用程序错误(-20001,不是上班时间,不能修改部门表);

结束如果;

结束;

限定只对部门号为80的记录进行行触发器操作。

创建或替换触发器tr _ emp _ sal _ comm

更新薪金之前,佣金百分比

或者删除

关于人力资源员工

对于每一行

WHEN (old.department_id=80)

开始

情况

更新(薪金)时,则

IF :NEW.salary :old.salary THEN

引发应用程序错误(-20001,部门80的人员的工资不能降);

结束如果;

当更新(佣金百分比)时,则

如果:新的。佣金百分比:旧。佣金百分比然后

引发应用程序错误(-20002,部门80的人员的奖金不能降);

结束如果;

当删除然后

引发应用程序错误(-20003,不能删除部门80的人员记录);

结束案例;

结束;

/*

实例:

更新员工设定工资=8000,其中employee _ id=177

从(177,170)中员工id所在的雇员中删除;

*/

利用行触发器实现级联更新。在修改了主表地区中的区域标识之后(之后),级联的、自动的更新子表国家表中原来在该地区的国家的区域标识。

创建或替换触发器tr_reg_cou

更新区域标识后

关于区域

对于每一行

开始

DBMS _ OUTPUTPUT _ LINE(旧的区域标识值是||:旧的。区域标识

||、新的区域标识值是| |:新建。region _ id);

更新国家/地区集区域标识=:新的区域标识

其中region _ id=:旧。区域标识;

结束;

在触发器中调用过程。

创建或替换过程添加作业历史记录

(p _ EMP _ id作业_历史记录。员工标识%类型

,p _ start _ date作业_历史记录。开始日期%类型

p _ end _ date作业历史记录。结束日期%类型

,p _ job _ id作业_历史。job _ id %类型

,p _ department _ id作业_历史记录。部门标识%类型

)

开始

插入到职务历史记录(员工id,开始日期,结束日期,

职务标识,部门标识)

值(p _员工_id,p _开始_日期,p _结束_日期,p _工作_id,p _部门_ id);

结束添加作业历史记录

-创建触发器调用存储过程.

创建或替换触发器更新_作业_历史记录

更新员工的职务标识、部门标识后

对于每一行

开始

add _ job _ history(:old . employee _ id,old.hire_date,sysdate,

:old.job_id,old . department _ id);

结束;

这就是本文的全部内容。希望对大家的学习有帮助,支持我们。

oracle中的触发器,oracle的trigger