Oracle触发器详细介绍一
触发器
是特定事件出现的时候,自动执行的代码块。类似于存储过程,但是用户不能直接调用他们。
功能:
1、 允许/限制对表的修改
2、 自动生成派生列,比如自增字段
3、 强制数据一致性
4、 提供审计和日志记录
5、 防止无效的事务处理
6、 启用复杂的业务逻辑
开始
create trigger biufer_employees_department_id
before insert or update
of department_id
on employees
referencing old as old_value
new as new_value
for each row
when (new_value.department_id<>80 )
begin
:new_value.commission_pct :=0;
end;
/
触发器的组成部分:
1、 触发器名称
2、 触发语句
3、 触发器限制
4、 触发操作
1、 触发器名称
create trigger biufer_employees_department_id
命名习惯:
biufer(before insert update for each row)
employees 表名
department_id 列名
2、 触发语句
比如:
表或视图上的DML语句
DDL语句
数据库关闭或启动,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的时候
3、 触发器限制
when (new_value.department_id<>80 )
限制不是必须的。此例表示如果列department_id不等于80的时候,触发器就会执行。
其中的new_value是代表更新之后的值。
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;
触发器不会通知用户,便改变了用户的输入值。
触发器类型:
1、 语句触发器
2、 行触发器
3、 INSTEAD OF 触发器
4、 系统条件触发器
5、 用户事件触发器
注释:
before和after:指在事件发生之前或之后激活触发器。
instead of:如果使用此子句,表示可以执行触发器代码来代替导致触发器调用的事件。
insert、delete和update:指定构成触发器事件的数据操纵类型,update还可以制定列的列表。
referencing:指定新行(即将更新)和旧行(更新前)的其他名称,默认为new和old。
table_or_view_name:指要创建触发器的表或视图的名称。
for each row:指定是否对受影响的每行都执行触发器,即行级触发器,如果不使用此子句,则为语句级触发器。
when:限制执行触发器的条件,该条件可以包括新旧数据值得检查。
declare---end:是一个标准的PL/SQL块。
Oracle触发器详细介绍二--语句触发器
语句触发器
是在表上或者某些情况下的视图上执行的特定语句或者语句组上的触发器。能够与INSERT、UPDATE、DELETE或者组合上进行关联。但是无论使用什么样的组合,各个语句触发器都只会针对指定语句激活一次。比如,无论update多少行,也只会调用一次update语句触发器。
例子:
需要对在表上进行DML操作的用户进行安全检查,看是否具有合适的特权。
Create table foo(a number);
Create trigger biud_foo
Before insert or update or delete
On foo
Begin
If user not in (‘DONNY’) then
Raise_application_error(-20001, ‘You don’t have access to modify this table.’);
End if;
End;
/
即使SYS,SYSTEM用户也不能修改foo表
[试验]
对修改表的时间、人物进行日志记录。
1、 建立试验表
create table employees_copy as select *from hr.employees
2、 建立日志表
create table employees_log(who varchar2(30), when date);
3、 在employees_copy表上建立语句触发器,在触发器中填充employees_log 表。
Create or replace trigger biud_employee_copy
Before insert or update or delete
On employees_copy
Begin
Insert into employees_log(Who,when) Values( user, sysdate);
End;
/
4、 测试
update employees_copy set salary= salary*1.1;
select *from employess_log;
5、 确定是哪个语句起作用?
即是INSERT/UPDATE/DELETE中的哪一个触发了触发器?
可以在触发器中使用INSERTING / UPDATING / DELETING 条件谓词,作判断:
begin
if inserting then
-----
elsif updating then
-----
elsif deleting then
------
end if;
end;
if updating(‘COL1’) or updating(‘COL2’) then
------
end if;
[试验]
1、 修改日志表
alter table employees_log add (action varchar2(20));
2、 修改触发器,以便记录语句类型。
Create or replace trigger biud_employee_copy
Before insert or update or delete
On employees_copy
Declare
L_action employees_log.action%type;
Begin
if inserting then
l_action:=’Insert’;
elsif updating then
l_action:=’Update’;
elsif deleting then
l_action:=’Delete’;
else
raise_application_error(-20001,’You should never ever get this error.’);
Insert into employees_log( Who,action,when) Values( user, l_action,sysdate);
End;
/
3、 测试
insert into employees_copy( employee_id, last_name, email, hire_date, job_id)
values(12345,’Chen’,’Donny@hotmail’,sysdate,12);
select *from employees_log
总结:语句级触发器.(语句级触发器对每个DML语句执行一次)是在表上或者某些情况下的视图上执行的特定语句或者语句组上的触发器。能够与INSERT、UPDATE、DELETE或者组合上进行关联。但是无论使用什么样的组合,各个语句触发器都只会针对指定语句激活一次。比如,无论update多少行,也只会调用一次update语句触发器。
实例:
create or replace trigger tri_test
after insert or update or delete on test
begin
if updating then
dbms_output.put_line('修改');
elsif deleting then
dbms_output.put_line('删除');
elsif inserting then
dbms_output.put_line('插入');
end if;
end;
Oracle触发器详细介绍三--行级触发器
行级触发器
本章介绍行级触发器机制。大部分例子以INSERT出发器给出,行级触发器可从insert update delete语句触发。
1、介绍
触发器是存储在数据库已编译的存储过程,使用的语言是PL/SQL,用编写存储过程一样的方式编写和编译触发器。下面在SQL*PLUS会话中创建和示例一个简单的Insert行级触发器。这个触发器调用DBMS_OUTPUT在每插入一行数据时打印 “executing temp_air”
SQL> set feedback off
SQL> CREATE TABLE temp (N NUMBER);
SQL> CREATE OR REPLACE TRIGGER temp_air
2 AFTER INSERT ON TEMP
3 FOR EACH ROW
4 BEGIN
5 dbms_output.put_line('executing temp_air');
6 END;
7 /
8 SQL> INSERT INTO temp VALUES (1); -- insert 1 row
executing temp_air
SQL> INSERT INTO temp SELECT * FROM temp; -- insert 1 row
executing temp_air
SQL> INSERT INTO temp SELECT * FROM temp; -- inserts 2 rows
executing temp_air
executing temp_air
SQL>
尽管第三个Insert语句是一条SQL语句,但插入TEMP表中两条记录。许多insert语句插入一条记录,但可以用一条语句插入许多行。
2、行级触发器语法:
CREATE OR REPLACE TRIGGER trigger_name
AFTER|BEFORE INSERT|UPDATE|DELETE ON table_name
FOR EACH ROW
[WHEN (Boolean expression)]
DECLARE
Local declarations
BEGIN
Trigger Body written PL/SQL
END;
•Trigger_name
用触发器名来确定表名和触发器类型。PL/SQL运行时错误将产生一个PL/SQL错误信息,涉及触发器名和行数。下面Oracle错误显示了在students表上的AFTER-INSERT行触发器的第5行有一个被0除错误。
ORA-01476: divisor is equal to zero
ORA-06512: at "SCOTT.STUDENTS_AIR", line 5
ORA-04088: error during execution of trigger
'SCOTT.STUDENTS_AIR'
行记数从关键字DECLARE行开始,如果没有DECLARE部分,BEGIN语句是第一行。触发器名称存储在USER_TRIGGERS表的TRIGGER_NAME。触发器名一般由表名、触发器类型、触发事件,语法如下:
trigger_name = table_name_[A|B] [I|U|D] [R|S]
trigger_name 最长30个字符,所以有时不得不使用表名缩写。常表名一般要有一个规则的缩写。这样可以减少故障分析处理时间。
[A|B] 表示是AFTER 或 BEFORE 触发器类型
[I|U|D] 表示触发事件,可能是 insert ,update 或者delete
[R|S] 表示行级(row)或语句级(statement)触发器类型。
•BEFORE|AFTER insert on table_name
这条语句告诉Oracle什么时候执行触发器.它可能在ORACLE 完整性约束检查前或后执行,可以指定一个Before或after触发器在多语句操作类型上触发,如:
BEFORE INSERT OR UPDATE on table_name
BEFORE INSERT OR UPDATE OR DELETE on table_name
AFTER INSERT OR DELETE on table_name
DBMS_STANDARD 包提供了四个boolean函数来区分SQL语句类型。
PACKAGE DBMS_STANDARD IS
FUNCTION inserting RETURN BOOLEAN;
FUNCTION updating RETURN BOOLEAN;
FUNCTION updating (colnam VARCHAR2) RETURN BOOLEAN;
FUNCTION deleting RETURN BOOLEAN;
etc,
END DBMS_STANDARD;
在触发器中可以直接使用函数名称,不需要指定包名:
CREATE OR REPLACE TRIGGER temp_aiur
AFTER INSERT OR UPDATE ON TEMP
FOR EACH ROW
BEGIN
CASE
WHEN inserting THEN
dbms_output.put_line
('executing temp_aiur - insert');
WHEN updating THEN
dbms_output.put_line
('executing temp_aiur - update');
END CASE;
END;
对于Update行级触发器,可以指定被更新的列作为触发器触发条件。
CREATE OR REPLACE TRIGGER temp_aur
AFTER INSERT OR UPDATE OF M, P ON TEMP
FOR EACH ROW
BEGIN
dbms_output.put_line
('after insert or update of m, p');
END;
•WHEN(BOOLEAN EXPRESSION)
这是个可选语句,用来过滤触发触发器的条件。
CREATE OR REPLACE TRIGGER temp_air
AFTER INSERT ON TEMP
FOR EACH ROW
WHEN (NEW.N = 0)
BEGIN
dbms_output.put_line('executing temp_air');
END;
上例中表示AFTER INSERT行触发器触发的条件是:N字段的值等于0.
NEW.COLUMN_NAME : INSERT或UPDATE触发器中WHEN语句中引用字段的语法。
OLD.COLUMN_NAME : 用于UPDATE或DELETE行级触发器中WHEN语句中。在INSERT语句中为Null。
Oracle触发器详细介绍四--INSTEAD OF触发器
在简单视图上往往可以执行INSERT、UPDATE和DELETE操作,但是在复杂视图上执行INSERT、UPDATE和DELETE操作是有限的。如果视图子查询包含有集合操作符、分组函数、DISTINCT关键字或者连接查询,那么将禁止在该视图上执行DML操作。为了在这些复杂视图上执行操作,需要建立INSTEAD-OF触发器。
INSTEAD-OF触发器具有以下限制:
INSTEAD OF触发器只适用于视图。
INSTEAD OF触发器不能指定BEFORE和AFTER选项。
不能在具有WITH CHECK OPTION选项的视图上建立INSTEAD OF触发器。
INSTEAD OF触发器必须包含有FOR EACH ROW选项。
复杂视图DEPT_EMP用于显示部门号、部门名、雇员号以及雇员名,并且在该复杂视图上不能执行任何DML操作。为了在该视图上执行DML操作,必须建立INSTEAD OF触发器。下面以完成该认务为例,说明建立INSTEAD OF触发器的方法。在建立INSTEAD OF触发器之前,首先建立视图DEPT_ENP。
create or replace view dept_emp as
select a.deptno,a.dname,b.empno,b.ename from department a,employee b
where a.deptno=b.deptno;
create or replace trigger tr_instead_of_dept_emp
instead of insert on dept_emp for each row
declare
v_temp int;
begin
select count(*) into v_temp from department where deptno=:new.deptno;
if v_temp=0 then
insert into department(deptno,dname) values(:new.deptno,:new.dname);
end if;
select count(*) into v_temp from employee where empno=:new.empno;
if v_temp=0 then
insert into employee(empno,ename,deptno) values(:new.emptno,:new.deptno);
end if;
end;
/
Oracle触发器详细介绍五--系统事件触发器
oracle的系统事件触发器:系统事件触发器是指基于oracle系统事件(如logon和startup)所建立的触发器。通过这种触发器可以跟踪系统或数据库的变化。
create table jax_event_table(eventname varchar2(30),time date);
create trigger tr_startup
after startup on database
begin
insertinto jax_event_table values(ora_sysevent,sysdate);
end;
create trigger tr_shutdown
before shutdown on database
begin
insert into jax_event_table values(ora_sysevent,sysdate);
end;
在建立如上所示的两个触发器后,使用shutdown和startup关闭开启数据库会往表jax_event_table中记录一条记录,但 shutdown abort则不会触发该触发器,而startup nomount后使用alter database将数据库更改为mount或者open都只会触发一次。
1 SHUTDOWN 2008-3-20 14:29:47
2 STARTUP 2008-3-20 14:42:52
3 SHUTDOWN 2008-3-20 14:43:06
4 STARTUP 2008-3-20 14:45:34
登录和退出触发器用来记载登录用户名称、时间和ip地址
Create table jax_log_table( username varchar2(20), log_time date, onoff varchar(6), address varchar2(30));
create trigger tr_logon
after logon ondatabase
begin
insert into jax_log_table values(ora_login_user,sysdate,'logon',ora_client_ip_address);
end;
create trigger tr_logoff
before logoff ondatabase
begin
insert into jax_log_table values(ora_login_user,sysdate,'logoff',ora_client_ip_address);
end;
select * from jax_log_table;
1 SYS 2008-3-20 14:55:17 logon
2 SYSMAN 2008-3-20 14:55:21 logon
3 SYS 2008-3-20 14:55:45 logon 127.0.0.1
4 SYS 2008-3-20 14:56:07 logoff
5 SYSMAN 2008-3-20 14:56:26 logon
6 SYSMAN 2008-3-20 14:56:27 logoff
7 ZHANGLEI 2008-3-20 14:56:35 logon 127.0.0.1
8 ZHANGLEI 2008-3-20 14:57:01 logoff
9 SYS 2008-3-20 14:57:12 logon 127.0.0.1
10 SYSMAN 2008-3-20 14:57:31 logon
11 SYSMAN 2008-3-20 14:57:32 logoff
DDL触发器记录系统所发生的DDL事件(create,alter,drop等)
create table jax_event_ddl_table(event varchar2(20), username varchar2(10),owner varchar2(10), objname varchar2(20), objtype varchar2(10),timedate);
create trigger tr_ddl
after ddl on database
begin
insert into jax_event_ddl_table values(ora_sysevent,ora_login_user,
ora_dict_obj_owner,ora_dict_obj_name,ora_dict_obj_type,sysdate)
end;
1 CREATE SYS SYS T2 TABLE 2008-3-20 15:05:41
2 CREATE SYS SYS VIEW_T2 VIEW 2008-3-20 15:06:53
由上面的描述看到,在编写系统事件触发器时,应用开发人员经常需要使用事件属性函数。常用的事件属性函数如下:
Ora_client_ip_address 返回客户端的ip地址
Ora_database_name 返回当前数据库名
Ora_des_encrypted_password 返回des加密后的用户口令
Ora_dict_obj_name 返回ddl操作所对应的数据库对象名
Ora_dict_obj_name_list(name_list out ora_name_list_t) 返回在事件中被修改的对象名列表
Ora_dict_obj_owner 返回ddl操作所对应的对象的所有者名
Ora_dict_obj_owner_list(owner_list out ora_name_list_t) 返回在事件中被修改的对象的所有者列表
Ora_dict_obj_type 返回ddl操作所对应的数据库对象的类型
Ora_grantee(user_list out ora_name_list_t) 返回授权事件的授权者
Ora_instance_num 返回例程号
Ora_is_alter_column(column_name in varchar2) 检测特定列是否被修改
Ora_is_creating_nested_table 检测是否正在建立嵌套表
Ora_is_drop_column(column_name in varchar2) 检测特定列是否被删除
Ora_is_servererror(error_number) 检测是否返回了特定oracle错误
Ora_login_user 返回登录用户名
Ora_sysevent 返回触发器的系统事件名。
分享到:
相关推荐
下面我们将详细探讨如何调试Oracle触发器。 1. **PL/SQL Developer工具调试**: - PL/SQL Developer是一款强大的Oracle开发工具,它提供了友好的图形用户界面来帮助我们调试触发器。在文档中提到的`TestWindow`...
总之,"Oracle触发器实时调用Java中HTTP接口"是一个数据库与应用程序实时通信的示例,涉及Oracle的触发器、存储过程和UTL_HTTP包,以及Java HTTP服务器的设计和实现。这样的设计需要对Oracle PL/SQL和Java网络编程有...
### Oracle触发器Before和After应用详解 #### 引言 Oracle数据库系统中,触发器是一项核心功能,用于响应特定事件的自动执行代码块。本文旨在深入解析Oracle触发器中的Before和After两种触发时机,以及它们在...
Oracle触发器是数据库对象之一,它在特定的数据库操作(如INSERT、UPDATE或DELETE)发生时自动执行。触发器主要用于实现复杂的业务规则和数据验证,它们可以扩展SQL的功能,允许在数据修改前后执行一系列的动作。...
1、行级触发器不支持 update 、select 、delete 对自身表的操作。 2、表级触发器 不支持 :new 和 :old对象 所以想要触发器对自身表数据做修该,则用行级触发器得到 :new 和 :old对象中的相关数据,然后将这样的数据...
在本文中,我们将介绍如何使用Oracle触发器调用Java程序。 一、加载Java程序 首先,我们需要将Java程序加载到Oracle数据库中。我们可以使用LOADJAVA命令来实现这一步骤。LOADJAVA命令位于$ORACLE_HOME/bin目录下,...
"Oracle触发器语法详解" Oracle 触发器是一种特殊的存储过程,它在插入、删除或修改特定表中的数据时触发执行,它比数据库本身标准的功能有更精细和更复杂的数据控制能力。触发器可以基于数据库的值使用户具有操作...
"oracle触发器的种类和触发事件" Oracle触发器是一种特殊的存储过程,它可以在数据库中自动执行一些操作,主要用于实现数据的完整性、数据的安全性和业务逻辑的实现。触发器可以分为四种类型:DML触发器、DDL触发器...
【Oracle触发器的创建与使用】是数据库管理中不可或缺的一部分,尤其在Oracle数据库系统中,触发器扮演着关键角色。触发器是一种存储过程,当特定的数据库事件发生时(如INSERT、UPDATE或DELETE操作),它会被自动...
《Oracle触发器与存储过程高级编程》第3版是一本深入探讨Oracle数据库中触发器和存储过程技术的专业书籍。在Oracle数据库系统中,触发器和存储过程是数据库管理员和开发人员进行复杂业务逻辑处理和数据管理的重要...
Oracle触发器是数据库管理系统中的一种特殊程序,它与数据库表的操作紧密相关,可以在特定的数据操作事件(如INSERT、UPDATE或DELETE)发生时自动执行。在本实验报告中,主要涉及了Oracle触发器与参照完整性约束的...
以下详细介绍两种不同的Oracle版本(10g和8i)下如何创建相应的系统触发器。 ### Oracle 10g环境下的解决方案 在Oracle 10g环境中,可以创建一个系统级别的触发器,用于阻止非`SYS`用户执行`DROP TABLE`或`...
Oracle 触发器实例讲解 Oracle 触发器是一种特定事件出现的时候,自动执行的代码块。类似于存储过程,但是用户不能直接调用他们。它主要有以下几个功能: 1. 允许/限制对表的修改:触发器可以控制对表的修改操作,...
在深入探讨如何利用Oracle触发器备份表数据之前,我们首先需要理解几个关键概念:Oracle数据库、触发器以及备份策略。Oracle数据库是全球领先的数据库管理系统之一,以其强大的性能、可靠的安全性和丰富的功能受到...
### Oracle触发器语法要点 #### 一、触发器概述 Oracle触发器是一种存储过程,它在特定的数据库事件(如INSERT、UPDATE或DELETE)发生时自动执行。触发器可以用于实施复杂的业务规则、数据完整性检查或者自动生成...
"Oracle触发器调用存储过程" Oracle触发器可以调用存储过程,以实现业务逻辑的自动化执行。然而,在触发器中调用存储过程时,需要注意事务的隔离性,以避免出现锁定和死锁的问题。Oracle自治事务(Autonomous ...
Oracle触发器可以响应数据表的DML(数据操纵语言)事件,如INSERT、UPDATE或DELETE,也可以响应DDL(数据定义语言)事件,如CREATE、ALTER或DROP等。在本文件中,主要讨论的是针对数据表UPDATE操作的触发器执行顺序...
Oracle触发器与存储过程高级编程 中文 PDF
在深入探讨《Oracle触发器与存储过程高级编程》这一主题之前,我们首先应当明确Oracle数据库在企业级应用中的核心地位。Oracle数据库以其强大的数据处理能力、高可用性和安全性,成为众多大型企业和组织首选的数据...
本篇文章将探讨如何在Oracle触发器中调用Java程序,以扩展数据库的功能并利用Java丰富的库资源。 首先,我们需要了解Oracle的PL/SQL(Procedural Language/Structured Query Language)语言,它是Oracle数据库内置...