重温Oracle(9)

 

第二十三章 触发器

 

存放在数据库中，并被隐含执行的存储过程。在oracle8i之前，只允许给予表或者视图的DML的操作，而从8i开始，不仅可以支持DML触发器，也允许给予系统事件和DDL的操作。

 

语句触发器

Defore语句触发器

Create or replace trigger tr_src_emp

before insert or update or delete on emp

begin

  if to_char(sysdate,’dy’,’nls_language=AMERICAN’) in(‘STA’,’SUN’) then raise_application_error(-20001,’can’t modify user information in weekend’);

end if;

end;

/

 

条件触发器

Create or replace trigger tr_src_emp

before insert or update or delete on emp

begin

  if to_char(sysdate,’dy’) in (’星期六’,’星期天’) then

case

when inserting then

raise_application_error(-20001,’fail to insert’);

         when updating then

         when deleting then

         end case;

end if;

end;

/

 

 

Alter语句触发器

统计EMP表上的增删改的次数，先建一张表

Create table audit_table( name varchar2(20),ins int,udp int,del int,starttime date,endtime date);

 

建立触发器

Create or replace trigger tr_audit_emp

After insert or update or delete on emp

Declare

V_temp int;

Begin

Select count(*) into v_temp from audit_table where name=’EMP’;

If v_temp=0 then

  Insert into audit_table values(‘EMP’,0,0,0,sysdate, null);

End if;

Case

 When inserting then

  Update audit_table set ins=ins+1,endtime=sysdate

 

行触发器

执行DML操作时，每作用一行就出发一次。

Before行触发器

Create or replace trigger tr_emp_sal

Before/after update of sal on emp

For each row

Begin

If :new.sal <:0ld.sal then

  Raise_application_error(-20010,’sal should not be less’);

End if;

End;

/

 

注意事项：

编写DML触发器的时候，触发器代码不能从触发器所对应的基表读取数据。

 

触发器的主要用途

控制数据安全：例如在非工作时间不能对表进行操作等。

实现数据统计：例如记载员工的工资变化等。

实现数据的完整性：如果限制员工的工资不能低于800，可以选择check约束，但如果是限定新工资不能低于其原来工资，也不能高于20%，则无法通过约束实现。

实现参照完整性：约束可实现级联删除，却不能实现级联更新。

 

系统事件触发器

系统事件触发器是指基于Oracle系统事件（例如LOGON和STARTUP）所建立的触发器。通过使用系统事件触发器，提供了跟踪系统或数据库变化的机制。下面介绍一些常用的系统事件属性函数，以及建立各种事件触发器的方法。

1， 常用事件属性函数

建立系统事件触发器时，应用开发人员经常需要使用事件属性函数。常用的时间属性函数如下：

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_after_column(column_name IN VARCHAR2)：用于检测特定列是否被修改；

Ora_is_creating_nested_table：用于检测是否正在建立嵌套表；

Ora_is_drop_column(column_name IN varchar2)：用于检测特定列是否被删除；

Ora_isservererror(error_number)：用于检测是否返回了特定Oracle错误；

Ora_login_user：用于返回登录用户名；

Ora_sysevent：用于返回触发触发器的系统事件名。

2，  案例启动和关闭触发器

记载实例启动和关闭的事件和时间，首先建立事件表event_table，示例如下：

SQL>conn sys/oracle as sysdba

SQL>create table event_table(event varchar2(30), time date);

在建立了事件表之后，就可以在触发器中引用该表了。注意，实例启动触发器和实例关闭触发器只有特权用户才能建立，并且实例启动触发器只能使用after关键字，二实例关闭触发器只能使用before关键字。

Create or replace trigger tr_startup

After startup on database

Begin

  Insert into event_table values(ora_sysevent, sysdate);

End;

/

Create or replace trigger tr_shutdown

Before shutdown on database

Begin

  Insert into event_table values(ora_sysevent, sysdate);

End;

/

在建立了启动/关闭触发器之后，当打开数据库之后/关闭实例之前，会执行该触发器的相应代码，但shutdown abort命令不会触发该触发器。

3， 登录和退出触发器

为了记录用户登录和退出事件，可以分别建立登录和退出触发器。为了记载登录用户和退出用户的名称、时间和IP地址，应该首先建立专门存放登录和退出的信息表log_table。示例如下：

SQL>conn sys/oracle as sysdba

SQL>create table log_table(username varchar2(20,logon_time date,logoff_time date,address varchar2(20));

在建立了表之后，就可以再触发器中引用该表了。注意，登录触发器和退出触发器一定要以特权用户身份建立，并且登录触发器只能在after关键字，而退出触发器只能使用before关键字。示例如下：

Create or replace trigger tr_logon

After logon on database

Begin

  Insert into log_table(username,logon_tine,address) values(ora_login_user,sysdate,ora_client_ip_address);

End;

/

Create or replace trigger tr_logoff

After logoff on database

Begin

  Insert into log_table(username,logoff_tine,address) values(ora_login_user,sysdate,ora_client_ip_address);

End;

/

在建立了登录/登出触发器之后，当用户登录到数据库之后/断开数据库连接之前，会执行器触发器代码。

4，  DDL触发器

为了记载系统所发生的DDL事件（CREATE,ALTER,DROP等），可以建立DDL触发器。为了记载DDL事件，应该建立专门的表，以便存放DDL事件信息。示例如下：

SQL>conn sys/oracle as sysdba

SQL>create table event_ddl(event varchar2(20),username varchar2(10),owner varchar2(10),objname varchar2(20),objtype varchar2(10),time date);

建立了表之后，就可以再触发器中引用该表了。注意，当建立DDL触发器时，必须使用after关键字。示例如下：

Create or replace trigger tr_ddl

After ddl on scott.schema

Begin

  Insert into event_ddl values(ora_sysevent,ora_login_user,ora_dict_obj_owner,ora_dict_obj_name,ora_dict_obj_type,sysdate);

End;

/

在建立了触发器之后，如果在SCOTT方案对象上执行了DDL操作，则会将该信息记录到表中。

 

管理触发器

显示触发器信息：建立触发器时，oracle会将触发器信息写入到数据字典中，通过查询数据字典视图USER_TRIGGERS，可以显示当前用户所包含的所有触发器信息。

禁止触发器：禁止触发器是指使触发器临时失效。当触发器处于enable状态时，如果在表上执行DML操作，则就会触发相应的触发器。如果给予insert操作建立了触发器，当使用SQL*Loader状态大批量数据时会触发触发器。如果加快数据装载速度，应该在装载数据之前禁止触发器。禁止/激活触发器SQL如下：        SQL>alter trigger tr_check_sal disable/enable;

禁止或激活表的所有触发器：SQL>alter table emp disable/enable all triggers;

重新编译触发器：当使用alter table命令修改表结构(例如增加或删除列)时，会使得其触发器转变为invalid状态。在这种情况下需要重新编译触发器。SQL>alter trigger tname compile;

删除触发器：在表上的触发器也刴，对于DML操作的性能影响也越大，因此当触发器不在需要时，应及时删除。SQL>drop trigger tr_check_sal;

 

第二十三章 事务（数据库系统概论）

 

事务控制

在oracle中每个连接都会产生一个session，一个session对数据库的修改，不会立刻反映到数据库的真实数据上，是允许回滚的。只有当提交了，才变成持久数据了。

可能出现死锁的情况。

事务的特性：原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)

 

原子性和一致性的差别

原子性：只一个事务中，包含若干个数据草组，这些操作是一个整体，要不一起完成，要不一起不完成，不能只完成其中的一部分。比如银行转账，A账户减操作和B账户加操作要不都成功，要不都失败。

一致性：是指数据库从一个完整的状态跳到另一个完整的状态，是用于保护数据库的完整性的。比如外键修改，如果外键表没有此信息，则违反了数据库的一致性。

 

 

张文海