Oracle触发器
很多关系数据库中都提供一种技术,可以在用户进行某种操作的时候,自动的进行另外一个操作,这种技术称为触发器技术。当发生特定事件时(如修改表、建立对象、登录到数据库),oracle就会自动的去执行相应的代码。
触发器分类:
1、dml触发器;
2、系统事件触发器;
3、ddl触发器;
触发器由触发事件,触发条件,触发操作三个部分构成。
create [or replace] trigger 触发器名称
{before|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带上则为覆盖
before在触发事件之前执行
after在触发事件之后执行
insert/delete/update在插入、删除、更新操作时触发
or可以多个操作同时定义触发器
on对哪一个表或视图进行监控
for each row带上是对每一条数据都记录
when condition 条件表达式。
dml触发器
--创建my_emp表
create table my_emp
(
id number, --id
name varchar2(32) --姓名
);
--在my_emp添加一条数据的时候提示'添加了一条记录'
create or replace trigger t1
after insert on scott.my_emp
begin
dbms_output.put_line('添加了一条数据');
end;
/
--在my_emp上添加数据
insert into my_emp values(1,'a');
insert into my_emp values(1,'a');
insert into my_emp values(1,'a');
--在my_emp修改多条数据时,多次提示'修改了一条数据'
create or replace trigger t2
after update on scott.my_emp
for each row
begin
dbms_output.put_line('修改了一条数据');
end;
/
--修改my_emp上每条数据的姓名为b
update my_emp set name = 'b';
--禁止在休息日删除员工信息
create or replace trigger t3
before delete on scott.emp2
begin
if to_char(sysdate,'day') in ('星期六','星期天') then
dbms_output.put_line('禁止在休息日删除员工信息');
raise_application_error(-20001,'禁止在休息日删除员工信息');
end if;
end;
/
--删除emp2上的smith
delete from emp2 where ename='SMITH';
特别注意:
procedure raise_application_error(error_number number,error_msg varchar2);
error_number[自定义]从-20000至-20999之间,这样就不会与oracle的任何错误代码发生冲突。error_msg[自定义]的长度不能超过2k,否则截取2k。
使用条件谓词
当触发器中同时包含多个触发事件(insert,update,delete)时,为了在触发器代码中区分具体的触发事件,可以使用三个条件:
inserting
updating
deleting
--条件谓词
--禁止在休息日对员工信息进行CUD操作
create or replace trigger t4
before
insert or update or delete on scott.emp2
begin
if to_char(sysdate,'day') in ('星期六','星期天') then
case
when inserting then
dbms_output.put_line('禁止在休息日新增员工信息');
raise_application_error(-20002,'禁止在休息日新增员工信息');
when updating then
dbms_output.put_line('禁止在休息日更改员工信息');
raise_application_error(-20002,'禁止在休息日更改员工信息');
when deleting then
dbms_output.put_line('禁止在休息日删除员工信息');
raise_application_error(-20002,'禁止在休息日删除员工信息');
end case;
end if;
end;
/
--删除emp2上的smith
delete from emp2 where ename='SMITH';
--在emp2上新增一个员工
insert into emp2 values(9999,'aa','clear',null,null,1000,100,10);
--修改emp2中smith的工资为1500
update emp2 set sal = 1500 where ename='SMITH';
使用:old和:new
:new 修饰符访问操作完成后的列的值
:old 修饰符访问操作完成前的列的值
特性 | insert | update | delete |
old | null | 有效 | 有效 |
new | 有效 | 有效 | null |
--修改emp2表员工薪水的时候,不能低于原有工资,否则,显示员工工资修改前和修改后的值
create or replace trigger t5
before update on scott.emp2
for each row
begin
if :new.sal < :old.sal then
dbms_output.put_line('工资不能低于原有工资');
raise_application_error(-20001,'工资不能低于原有工资');
else
dbms_output.put_line('原工资:' || :old.sal || ' 新工资:' || :new.sal);
end if;
end;
/
--修改emp2中smith的工资为2000
update emp2 set sal = 2000 where ename='SMITH';
--修改emp2中smith的工资为20
update emp2 set sal = 20 where ename='SMITH';
系统触发器
系统事件是指基于oracle事件(例如logon/logout和startup/shutdown)所建立的触发器。通过使用系统事件触发器,提供了跟踪系统或是数据库变化的机制。
下面介绍一些常用的系统事件属性函数,和建立各种事件触发器的方法在建立系统事件触发器时,需要使用事件属性函数,常用的事件属性函数如下:
ora_client_ip_address //返回客户端的ip
ora_database_name //返回数据库名称
ora_login_user //返回登陆用户名
ora_sysevent //返回触发器的系统事件名
ora_des_encrypted_password //返回用户des(md5)加密后的密码
--登录和退出的触发器
--创建一张保存用户登录或者退出情况的表
create table log_table
(
username varchar2(20), --用户名
logon_time date, --登录时间
logoff_time date, --退出时间
address varchar2(20) --IP地址
);
事件属性函数表
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进行审计或停止审计之前、之后触发 |
特别说明:系统触发器的级别较高,由系统管理员来创建。
--创建触发器
--1.登录触发器
create or replace trigger t6
after logon on database
begin
insert into log_table(username,logon_time,address) values(ora_login_user,sysdate,ora_client_ip_address);
end;
/
--2.退出触发器
create or replace trigger t7
before logoff on database
begin
insert into log_table(username,logoff_time,address) values(ora_login_user,sysdate,ora_client_ip_address);
end;
/
--查询表
select USERNAME,to_char(LOGON_TIME,'yyyy-mm--dd hh24:mi:ss'),to_char(LOGOFF_TIME,'yyyy-mm--dd hh24:mi:ss'),ADDRESS from system.log_table;
ddl触发器
创建ddl触发器基本语法:
create or replace trigger ddl触发器名称
after ddl on 方案名.schema
begin
执行语句;
end;
特别注意:ddl触发器需要system用户数据库管理员权限才可以建立。
--记录用户的ddl操作
--创建一张表,记录用户操作信息
create table my_ddl_record
(
event varchar2(64),
username varchar2(64),
ddl_time date
);
--用system用户创建触发器
create or replace trigger t8
after ddl on scott.schema
begin
insert into my_ddl_record values(ora_sysevent,ora_login_user,sysdate);
end;
/
--切换至scott用户
create table e1(id number);
--切换回system用户
select * from my_ddl_record;
特别说明:在oracle中dml语句需要手动commit(提交),如果没有手动提交,在正常退出客户端时,oracle会自动提交;ddl语句是自动commit(提交)。
管理触发器
--禁用触发器
alter trigger t5 disable;
--激活触发器
alter trigger t5 enable;
--禁用或是激活激活表的所有触发器
alter table emp2 disable all triggers;
alter table emp2 enable all triggers;
--删除触发器
drop trigger t5;
特别注意:触发器是针对所有客户端的操作,只要是对设置了触发器的表进行操作,在满足触发条件,均会触发相应的触发器。
----------参考《韩顺平玩转Oracle》