Oracle触发器

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》