本文主要详细介绍了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);
结束;
这就是本文的全部内容。希望对大家的学习有帮助,支持我们。
郑重声明:本文由网友发布,不代表盛行IT的观点,版权归原作者所有,仅为传播更多信息之目的,如有侵权请联系,我们将第一时间修改或删除,多谢。