oracle trigger (触发器)

最新推荐文章于 2023-05-25 20:56:58 发布

xiaoshang

最新推荐文章于 2023-05-25 20:56:58 发布

阅读量801

点赞数

分类专栏： Oracle 文章标签： oracle insert table 数据库 delete triggers

Oracle 专栏收录该内容

46 篇文章 0 订阅

订阅专栏

转载地址：http://nullpoint.iteye.com/blog/1142461

--触发器 trigger
--1.DML触发器 DML statements (DELETE, INSERT, UPDATE)
/*
对于一条dml sql，可能作用与多行，也可能只有一行。语句触发器对每条触发sql，触发器只执行一次；行级触发器是每作用一行就触发一次
触发器。

DML触发器定义：
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE |AFTER} --触发时机，dml sql语句前或后
{INSERT | UPDATE | DELETE]--触发事件，增，删，改或3者的任意组合
ON table_name --说作用的表，一个触发器只能作用与一个表，一个表可以有多个触发器，但是触发器越多，对dml效率有影响
[REFERENCIING OLD AS old NEW AS new] --更新数据，对新旧数据引用变量的设置，使用较少
[FOR EACH ROW] --是否指定为行级触发器
[WHEN ...]--指定满足特定条件时，触发器body才会执行
[DECLARE...]--声明块
BEGIN
--可执行块
--这里不要有针对上面on的表的DML SQL，这样会造成递归触发，无限循环下去
...executable statements...
END [trigger_name];

*/

--语句触发器 statement-level trigger 在表上针对某种DML操作建立了语句触发器目标是整个表
CREATE OR REPLACE TRIGGER emp_t_1
BEFORE INSERT OR UPDATE OR DELETE ON EMP
BEGIN
CASE--判断什么类型的触发器
WHEN INSERTING THEN
dbms_output.put_line('emp_t_1 insert triggerd');
WHEN UPDATING THEN
dbms_output.put_line('emp_t_1 update triggerd');
WHEN DELETING THEN
dbms_output.put_line('emp_t_1 delete triggerd');
END CASE;
END;
--执行下面的update语句，上面的触发器会被处罚
update emp set sal = sal*1 where empno=7788;

--after 语句触发器
/*
exp：统计一个表的DML操作次数，DML操作发生后，after触发器将次数+1
*/
--创建统计表
CREATE TABLE count_dml(
id int,table_name varchar2(30),nums int,dt date
);
CREATE OR REPLACE TRIGGER emp_t_2
AFTER INSERT OR UPDATE OR DELETE ON EMP
DECLARE
v_count int;--声明一个记录上次的变量
BEGIN
select nums into v_count from count_dml where lower(table_name)='emp';--会有NO_DATA_FOUND异常
IF v_count = 0 THEN--如果这个表中还没有这个表所对应的记录，就新添加一个
insert into count_dml values(1,'emp',0,sysdate);
END IF;
update count_dml set nums=(v_count+1) where lower(table_name)='emp';
dbms_output.put_line(v_count+1);
END;
update emp set sal = sal*1 where empno=7788;
commit;

--行级触发器,创建语句中加入 FOR EACH ROW
CREATE OR REPLACE TRIGGER emp_t_3
BEFORE UPDATE ON emp FOR EACH ROW --for each row 定义为行级触发器，dml sql语句有多少行受影响该触发器就执行多少次
BEGIN
/*
在行级触发器块中可以用 :new.columName 引用新数据的列值
:old.columName 引用旧数据的列值
对于不同类型的触发器 new和old可能代替不同的内容
insert：只有new，没有old .old is null
update：old和new就都有了
delete：只有old 没有new
*/
dbms_output.put_line(:new.sal||'--'||:old.sal);
END;
--执行下面的sql，上面定义的触发器会触发3次
update emp set sal = sal*1.1 where emp.deptno=10; --3 rows affects

--符合一定条件才执行触发器语句，使用WHEN 语句限定
CREATE OR REPLACE TRIGGER emp_t_4
BEFORE UPDATE ON emp
-- old和new是引用新旧数据的默认值，这里可以明确指定
REFERENCING OLD AS emp_old NEW AS emp_new
FOR EACH ROW
--WHEN 语句限制只有empno=7788，的才执行该触发器，在when语句中引用列值时前面不用加':';
WHEN (emp_new.empno=7788)
BEGIN
dbms_output.put_line(:emp_new.ename||'--'||:emp_old.empno);
END;
--触发触发器，只有7788的执行上面的语句
update emp set sal = sal*1.1 where emp.deptno=20;

/**new和old可以使用于before和after触发器。before触发器可以修改new中的列值，但是在after中不行，
因为trigger在中的sql已经执行完毕，生效了。如果before中修改了new中的列值，在after中是可以看到的。


语句触发器（statement-level trigger）和行级触发器（row-level trigger）的执行顺序：
当然顺序整体上是先BEFORE类型的触发器执行，后AFTER类型的执行。
如果一个表中每种触发器都出现执行顺序
BEFORE 语句触发器
BEFORE 行级触发器
AFTER 行级触发器
...多个行受影响，行级触发器执行多次
AFTER 语句触发器
*/

--触发器内代码尽量要简单，如果比较复杂的话可以将复杂的语句存放到过程中，触发器只调用过程

--2 DDL触发器 DDL statements (CREATE, ALTER, DROP)
/*
DDL触发器所作用的对象就不是某个具体的表了，而是针对某个SCHEMA|DATABASE
DDL触发器目的主要是为了记录DDL操作，
当然也可在触发器中生成异常，事务回滚，DDL操作失败

oracle 数据库系统事件属性函数:
ora_client_ip_address:客户端ip地址
ora_database_name:当前数据库名
ora_dict_obj_name:DDL操作所对应的数据库对象名
ora_dict_obj_owner:DDL操作对象的所有者
ora_dict_obj_type:DDL操作所对应的数据库对象的类型
ora_login_user:登录用户名
ora_sysevent:触发器的系统事件名称
...
用这些属性可以描述DDL操作
*/
--创建记录ddl操作的table
CREATE TABLE ddl_records(
event varchar2(30),
username varchar2(30),
owner varchar2(30),
objname varchar2(20),
objtype varchar2(10),
d_date date
)

CREATE OR REPLACE TRIGGER ddl_t
AFTER DDL ON SCHEMA --DLL也可以是CREATE,DROP,ALTER的任意组合，作用对象可以是当前SCHEMA ,也可以是DATABASE
BEGIN
insert into ddl_records values(
ora_sysevent,ora_login_user,ora_dict_obj_owner,
ora_dict_obj_name,ora_dict_obj_type,sysdate);
END;
--执行一个ddl操作
create table test_ddl_triger(id int);
--ddl_records表中插入一条数据
/*
event:create,username:scott,owner:scott
objname:test_ddl_triger,objtype:table
*/
drop table test_ddl_triger;--又插入一条数据
commit;

--3. 系统事件触发器 Database operations (SERVERERROR, LOGON, LOGOFF, STARTUP, SHUTDOWN)
/*
系统事件触发器和DML触发器的功能差不多，都是为了记录跟踪数据库的变化。
不过这些触发器有的对BEFORE后AFTER有限制：
STARTUP事件只能有AFTER触发器
SHUTDOWN只能有BEFORE触发器
LOGON只能有AFTER触发器
LOGOFF只能有BEFORE触发器
SERVERERROR只能有AFTER触发器
*/
--AFTER SERVERERROR trigger
/*
以下error 不会触发
ORA-00600 oracle内部错误
ORA-01034 oracle无法使用
ORA-01403 没有查询到数据
ORA-01422 返回多行数据
ORA-01423
ORA-04030

--触发器不会修复错误
--内建函数获取异常信息
ora_server_error(index) 返回error number，找不到返回0
ora_is_servererror(number) error number是否在异常stack中，也就是判断当前异常是否包含指定异常
ora_server_error_depth 异常中error的数量
ora_server_error_msg(index) 错误信息
...

*/
CREATE OR REPLACE TRIGGER error_echo
AFTER SERVERERROR ON SCHEMA
DECLARE
num number := SQLCODE;
BEGIN
FOR i IN 1..ora_server_error_depth LOOP
dbms_output.put_line('错误码：'||ora_server_error(i));
dbms_output.put_line('错误信息：'||ora_server_error_msg(i));
END LOOP;
END;

/*
CREATE OR REPLACE TRIGGER ddl_echo
AFTER DDL ON SCHEMA
BEGIN
dbms_output.put_line(SQLCODE);
IF ora_is_servererror(SQLCODE) THEN
dbms_output.put_line('error:'||sqlerrm);
ELSE
dbms_output.put_line('OK:'||sqlerrm);
END IF;
END;
*/
--检验error_echo触发器功能
create table t_t(t number);
drop table t_t;
commit;
insert into t_t values('xx');--会有异常，被打印出

--维护触发器

--查看触发器信息
--user_triggers 数据字典视图,基本上囊括了触发器定义是所用信息
select * from user_triggers;
--当前用户有权限查看到的所有触发器
select * from all_triggers;

--使触发器失效
ALTER TRIGGER trigger_name DISABLE;

--从新激活触发器
ALTER TRIGGER trigger_name ENABLE;

--针对某个表
--禁止所有的触发器
ALTER TABLE table_name DISABLE ALL TRIGGERS;
--激活所有
ALTER TABLE table_name ENABLE ALL TRIGGERS；

--从新编译触发器
ALTER TRIGGER trigger_name COMPILE;

--删除触发器
DROP TRIGGER trigger_name;

Oracle 触发器语法及实例

转载地址：http://blog.csdn.net/fafa211/article/details/3915512

Oracle触发器语法(一)

一 Oracle触发器语法

触发器是特定事件出现的时候，自动执行的代码块。类似于存储过程，触发器与存储过程的区别在于:存储过程是由用户或应用程序显式调用的,而触发器是不能被直接调用的。

功能：

1、允许/限制对表的修改

2、自动生成派生列，比如自增字段

3、强制数据一致性

4、提供审计和日志记录

5、防止无效的事务处理

6、启用复杂的业务逻辑

触发器触发时间有两种：after和before。

1、触发器的语法：

CREATE [OR REPLACE] TIGGER触发器名触发时间触发事件

ON表名

[FOR EACH ROW]

BEGIN

pl/sql语句

END

其中：

触发器名：触发器对象的名称。

由于触发器是自动执行的，因此该名称只是一个名称，没有实质的用途。

触发时间：指明触发器何时执行，该值可取：

before---表示在数据库动作之前触发器执行;

after---表示在数据库动作之后出发器执行。

触发事件：指明哪些数据库动作会触发此触发器：

insert：数据库插入会触发此触发器;

Oracle触发器语法(二)

update：修改会触发此触发器;

delete：数据库删除会触发此触发器。

表名：数据库触发器所在的表。

for each row：对表的每一行触发器执行一次。如果没有这一选项，则只对整个表执行一次。

2、举例：

下面的触发器在更新表auths之前触发，目的是不允许在周末修改表：

create triggerauth_secure before insert or update or delete //对整表更新前触发

on auths

begin

if(to_char(sysdate,'DY')='SUN'

RAISE_APPLICATION_ERROR(-20600,'不能在周末修改表auths');

end if;

end

例子：

CREATE OR REPLACE TRIGGER CRM.T_SUB_USERINFO_AUR_NAME AFTER UPDATE OF STAFF_NAME

ON CRM.T_SUB_USERINFO

REFERENCING OLD AS OLD NEW AS NEW

FOR EACH ROW

declare

begin

if :NEW.STAFF_NAME!=:OLD.STAFF_NAME then

begin

客户投诉

update T_COMPLAINT_MANAGE set SERVE_NAME=:NEW.STAFF_NAME where SERVE_SEED=:OLD.SEED;

客户关怀

update T_CUSTOMER_CARE set EXECUTOR_NAME=:NEW.STAFF_NAME

where EXECUTOR_SEED=:OLD.SEED;

客户服务

update T_CUSTOMER_SERVICE set EXECUTOR_NAME=:NEW.STAFF_NAME

where EXECUTOR_SEED=:OLD.SEED;

end;

end if;

end T_sub_userinfo_aur_name;

Oracle触发器语法(三)

二 Oracle触发器详解

开始：

create triggerbiufer_employees_department_id

beforeinsertorupdateofdepartment_idonemployees

referencingoldasold_value newasnew_value

for each row

when (new_value.department_id<>80 )

begin

:new_value.commission_pct :=0;

end;

1、触发器的组成部分：

1、触发器名称

2、触发语句

3、触发器限制

4、触发操作

1.1、触发器名称

create trigger biufer_employees_department_id

命名习惯：

biufer(before insert update for each row)

employees表名

department_id列名

1.2、触发语句

比如：

表或视图上的DML语句

DDL语句

Oracle触发器语法(四)

关闭或启动,startup shutdown等等　

before insert or update

of department_id

on employees

referencing old as old_value

new as new_value

for each row

说明：

1、无论是否规定了department_id，对employees表进行insert的时候

2、对employees表的department_id列进行update的时候

1.3、触发器限制

when (new_value.department_id<>80 )

限制不是必须的。此例表示如果列department_id不等于80的时候，触发器就会执行。

其中的new_value是代表更新之后的值。

1.4、触发操作

是触发器的主体　

begin

:new_value.commission_pct :=0;

end;

主体很简单，就是将更新后的commission_pct列置为0

触发：

insert into employees(employee_id,last_name,first_name,hire_date,job_id,email,

department_id,salary,commission_pct )

values( 12345,’Chen’,’Donny’, sysdate, 12, ‘donny@hotmail.com’,60,10000,.25);

select commission_pct from employees where employee_id=12345;

触发器不会通知用户，便改变了用户的输入值。

xiaoshang

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
oracle trigger (触发器)

转载地址：http://nullpoint.iteye.com/blog/1142461--触发器 trigger--1.DML触发器 DML statements (DELETE, INSERT, UPDATE)/* 对于一条dml sql，可能作用与多行，也可能只有一行。语句触发器对每条触发sql，触发器只执行一次；行级触发器是每作用一行就触发一次触
复制链接

扫一扫

专栏目录