oracle触发器
几个问题：
请大家考虑一个需求：当一个用户登录到oracle时，在一张表中记录登录到oracle的用户和登录时间等信息，怎么办？再一个需求：禁止用户在星期天对某一张天进行删除操作，怎么办？再一个需求：当用户在删除一张表的时候，自动把删除的记录备份到另外一张表中...
解决之道
很多关系数据库中都提供一种技术，可以在用户进行某种操作的时候，自动的进行另外一个操作，我们把这种技术称为触发器技术。
触发器是指存放在数据库中，被隐含执行的存储过程，可以支持dml触发器，还支持基于系统事件(启动数据库，关闭数据库，登陆)和ddl操作建立触发器。
当发生特定事件时(如修改表、建立对象、登陆到数据库)，oracle就会自动的去执行相应的代码。
触发器分类：
1、dml触发器；
2、系统事件触发器；
3、ddl触发器；
触发器由触发事件，触发条件，触发操作三个部分构成。
触发器--创建基本语法：
create [or replace] trigger trigger_name
{before|after}
{insert|delete|update[of column [,column...]]}
or {insert|delete|update[of column [,column...]]}
on [schema.] table_name|[schema.]view_name
[for each row]
[when condition]
begin
trigger_body;
end;
create [or replace] trigger 触发器名称
{befor|after}
{insert|delete|update[of column [,column...]]}
or {insert|delete|update[of column [,column...]]}
on [schema.] 表名|[schema.]视图
[for each row]
[when condition]
begin
执行语句;
end;详细说明：
or replace带上则为覆盖
befor在触发事件之前执行
after在触发事件之后执行
insert/delete/update在插入、删除、更新操作时触发
or可以多个操作同时定义触发器
on对哪一个表或视图进行监控
for each row带上是对每一条数据都记录
when condition 条件表达式。
快速入门
在某张表(my_emp)添加一条数据的时候，提示“添加了一条数据”
1、建表
create table my_emp(id number,name varchar2(32));
2、创建一个触发器
create or replace trigger trigger1
after insert on scott.my_emp
begin
dbms_output.put_line('添加了一条数据');
end;
在某张表(my_emp)修改多条数据的时候，提示多次“修改了数据”
行级触发器和语句级触发器的区别
在创建触发器的时候，带不带for each row
create or replace trigger trigger2
after update on scott.my_emp
for each row--这是一个行级触发器
begin
dbms_output.put_line('修改了数据');
end;
快速之门2
为了禁止工作人员在休息日改变员工信息，开发人员可以建立before语句触发器，从而实现数据的安全。
create or replace trigger trigger3
before insert or update or delete on scott.my_emp
for each row
begin
if to_char(sysdate,'day') in ('星期六','星期日') then
raise_application_error(-20001,'对不起，休息日不能对数据进行改动。');
end if;
end;
特别注意：
procedure raise_application_error(error_number_in in number,error_msg_in in varchar2);
error_number_in[自定义]从-20000至-20999之间，这样就不会与oracle的任何错误代码发生冲突。error_msg_in[自定义]的长度不能超过2k,否则截取2k。
触发器--dml触发器
使用条件谓词
当触发器中同时包含多个触发事件(insert,update,delete)时，为了在触发器代码中区分具体的触发事件，可以使用三个条件：
inserting
updating
deleting
为了禁止工作人员在休息日改变员工信息，开发人员可以建立before语句触发器，从而实现数据的安全，在给出提示时，明确提示用户是进行的insert、update还是delete操作。
create or replace trigger trigger4
before insert or update or delete on scott.my_emp
for each row
begin
case
when inserting then
raise_application_error(-20001,'对不起，不能对insert数据。');
when updating then
raise_application_error(-20002,'对不起，不能对update数据。');
when deleting then
raise_application_error(-20003,'对不起，不能对delete数据。');
end case;
end;
触发器--dml触发器
使用:old和:new
问题：当触发器被触发时，要使用被插入、更新或删除的记录中的列值，有时要使用操作前、后列的值。
:new 修饰符访问操作完成后的列的值
:old 修饰符访问操作完成前的列的值
|
特性 |
insert |
update |
delete |
|
old |
null |
有效 |
有效 |
|
new |
有效 |
有效 |
null |
案例：1、在修改emp表雇员的薪水时，显示雇员工资修改前和修改后的值。2、如何确保在修改员工工资不能低于原有工资。
create or replace trigger trigger5
before update on scott.emp
for each row
begin
if :new.sal<:old.sal then
dbms_output.put_line('工资不能低于原工资');
raise_application_error(-20004,'工资不能低于原工资');
else
dbms_output.put_line('原工资为：'||:old.sal||'现工资为：'||:new.sal);
end if;
end;
触发器--dml触发器课堂练习
编写一个触发器，保证当用户在删除一张表(emp)记录的时候，自动把删除的记录备份到另处一张表(emp_bak)中
create table emp_bak as select * from emp;
delete from emp_bak;
create or replace trigger trigger6
before delete on scott.emp
for each row
begin
insert into emp_bak values
(:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno);
dbms_output.put_line('删除的'||:old.empno||'数据已备份到emp_bak表中');
end;
实现精细化控制
编写一个新的触发器，如何控制员工的新工资不能低于原来的工资，同时也不能高出原来工资的20%，使用约束显示无法实现该规则。
create or replace trigger trigger7
before update on scott.emp
for each row
begin
if(:new.sal<:old.sal or :new.sal>:old.sal*1.2) then
raise_application_error(-20005,'新工资不能低于原工资或不能高出原工资
20%!');
end if;
end;
阻止把小于18岁的用户增加到数据库表中，请编写一个触发器完成上述任务。
create table users(id number primary key,name varchar2(32) not null,birthday date not null);
create or replace trigger trigger8
before insert on scott.users
for each row
begin
if add_months(:new.birthday,18*12)>sysdate then
raise_application_error(-20006,'年龄未满18岁，不能使用童工!');
end if;
end;
特别说明：在存储过程中可以使用oracle提供的系统函数。
触发器--系统触发器
系统事件是指基于oracle事件(例如logon/logout和startup/shutdown)所建立的触发器。通过使用系统事件触发器，提供了跟踪系统或是数据库变化的机制。
下面介绍一些常用的系统事件属性函数，和建立各种事件触发器的方法在建立系统事件触发器时，我们需要使用事件属性函数，常用的事件属性函数如下：
ora_client_ip_address //返回客户端的ip
ora_database_name //返回数据库名称
ora_login_user //返回登陆用户名
ora_sysevent //返回触发器的系统事件名
ora_des_encrypted_password //返回用户des(md5)加密后的密码
事件属性函数表
|
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 |
返回触发器的系统事件名 |
系统触发器创建基本语法：
create or replace trigger 系统触发器名称
after[before] logon[logoff] on datebase
begin
执行语句;
end;
详细说明：
after 事件之后触发
before 事件之前触发
logon 登陆触发
logoff 登出触发
startup 开启系统触发
shutdown关闭系统触发
下面给出系统触发器的种类和事件出现的时机（前或后）：
|
事件 |
允许的时机 |
说明 |
|
STARTUP |
AFTER |
启动数据库实例之后触发 |
|
SHUTDOWN |
BEFORE |
关闭数据库实例之前触发（非正常关闭不触发） |
|
SERVERERROR |
AFTER |
数据库服务器发生错误之后触发 |
|
LOGON |
AFTER |
成功登录连接到数据库后触发 |
|
LOGOFF |
BEFORE |
开始断开数据库连接之前触发 |
|
CREATE |
BEFORE，AFTER |
在执行CREATE语句创建数据库对象之前、之后触发 |
|
DROP |
BEFORE，AFTER |
在执行DROP语句删除数据库对象之前、之后触发 |
|
ALTER |
BEFORE，AFTER |
在执行ALTER语句更新数据库对象之前、之后触发 |
|
DDL |
BEFORE，AFTER |
在执行大多数DDL语句之前、之后触发 |
|
GRANT |
BEFORE，AFTER |
执行GRANT语句授予权限之前、之后触发 |
|
REVOKE |
BEFORE，AFTER |
执行REVOKE语句收权限之前、之后触犯发 |
|
RENAME |
BEFORE，AFTER |
执行RENAME语句更改数据库对象名称之前、之后触犯发 |
|
AUDIT/NOAUDIT |
BEFORE，AFTER |
执行AUDIT或NOAUDIT进行审计或停止审计之前、之后触发 |
系统触发器事件属性
|
事件属性\事件 |
Startup/Shutdown |
Servererror |
Logon/Logoff |
DDL |
DML |
|
事件名称 |
□＊ |
□＊ |
□＊ |
□＊ |
＊ |
|
数据库名称 |
□＊ |
|
|
|
|
|
数据库实例号 |
□＊ |
|
|
|
|
|
错误号 |
|
□＊ |
|
|
|
|
用户名 |
|
|
□＊ |
＊ |
|
|
模式对象类型 |
|
|
|
□＊ |
＊ |
|
模式对象名称 |
|
|
|
□＊ |
＊ |
|
列 |
|
|
|
|
□＊ |
除DML语句的列属性外，其余事件属性值可通过调用ORACLE定义的事件属性函数来读取。
|
函数名称 |
数据类型 |
说明 |
|
Ora_sysevent |
VARCHAR2(20) |
激活触发器的事件名称 |
|
Instance_num |
NUMBER |
数据库实例名 |
|
Ora_database_name |
VARCHAR2(50) |
数据库名称 |
|
Server_error(posi) |
NUMBER |
错误信息栈中posi指定位置中的错误号 |
|
Is_servererror(err_number) |
BOOLEAN |
检查err_number指定的错误号是否在错误信息栈中，如果在则返回TRUE，否则返回FALSE。在触发器内调用此函数可以判断是否发生指定的错误。 |
|
Login_user |
VARCHAR2(30) |
登陆或注销的用户名称 |
|
Dictionary_obj_type |
VARCHAR2(20) |
DDL语句所操作的数据库对象类型 |
|
Dictionary_obj_name |
VARCHAR2(30) |
DDL语句所操作的数据库对象名称 |
|
Dictionary_obj_owner |
VARCHAR2(30) |
DDL语句所操作的数据库对象所有者名称 |
|
Des_encrypted_password |
VARCHAR2(2) |
正在创建或修改的经过DES算法加密的用户口令 |
建立登陆和退出触发器
为了记录用户的登陆和退出事件，我们可以建立登陆和退出触发器为了记录用户名称、时间、ip地址。我们首先建立一张信息表。
特别说明：系统触发器的级别较高，由系统管理员来创建。
SQL>conn system/orcl as sysdba;
create table log_table(username varchar2(32),logon_time date,logoff_time date,ip_address varchar2(20));
--创建登陆触发器
create or replace trigger log_tri
after logon on database
begin
insert into log_table (username,logon_time,ip_address) values
(ora_login_user,sysdate,ora_client_ip_address);
end;
--创建退出触发器
create or replace trigger exit_tri
before logoff on database
begin
insert into log_table (username,logoff_time,ip_address) values
(ora_login_user,sysdate,ora_client_ip_address);
end;
触发器--ddl触发器
什么是ddl(data definition language)，说白了就是我们常用的create、alter和drop这些数据定义语句。
创建ddl触发器基本语法：
create or replace trigger ddl触发器名称
after ddl on 方案名.schema
begin
执行语句;
end;
请编写一个触发器，可以记录某个用户进行的ddl操作：
为了记录系统所发生的ddl事件，应该建立一张(my_ddl_event)用来存相关信息。
特别注意：ddl触发器需要system用户数据库管理员权限才可以建立。
SQL>conn system/orcl as sysdba;
create table my_ddl_event(event varchar2(20),username varchar2(20),time date);
create or replace trigger ddltri
after ddl on scott.schema
begin
insert into my_ddl_event values(ora_sysevent,ora_login_user,sysdate);
end;
特别说明：在oracle中dml语句需要手动commit(提交)，如果没有手动提交，在正常退出客户端时，oracle会自动提交；ddl语句是自动commit(提交)。
触发器--管理触发器
特别注意：管理触发器使用system登陆。
禁止触发器
是指让触发器临时失效。
alter trigger 触发器名称 disable;
激活触发器
alter trigger 触发器名称 enable;
禁止或是激活表的所有触发器
alter table 表名 disable all triggers;
alter table 表名 enable all triggers;
删除触发器
drop trigger 触发器名称;
特别注意：触发器是针对所有客户端的操作，只要是对设置了触发器的表进行操作，在满足触发条件，均会触发相应的触发器。
