Oracle--触发器

什么是触发器

触发求器是一组完成特定功能的动作。这些动作由数据库自动调用和执行。触发器所执行的动作一般是一组DML操作。，

语句触发器

语句触发器的左右对象一般是数据表。其触发动作是针对作用对象的Dml操作

创建语句触发器

create trigger 触发器名称 on 作用对象
before/after 触发动作
as
触发器操作

create or replace trigger tr_insert_student
before insert
on students
begin
	if user!='admin' then 
		raise_application_error(-20001,'权限不足，不能向表中插入数据');
	end if；
end；

raise_application_error(-20001,‘权限不足，不能向表中插入数据’);抛出错误提示，并禁止插入

触发器的作用对象与触发时机

Oracle中的触发器可以建立在数据表或视图之上

可以为表创建before和after类型的触发器，但对视图来说，不能为其创建before和after类型的触发器

触发器的触发操作仅包含insert 和 update 。不能针对select操作创建触发器。

多个激活动作

一个触发器可以建立在多个激活动作之上，可以利用or关键字指定多个激活动作

create or replace tirgger tr_student
before insert or update or delete
on students
begin
	if user!='admin' then 
		raise_application_error(-20001,'权限不足，不能向数据表中插入数据')；
	end if;
end;

触发器谓词

一个触发器的激活动作可能有多个，当需要分辨到底是什么动作激活了触发器。利用触发器谓词是一个很好的选择。

触发器谓词有3种：inserting，updating,deleting

1.inserting,如果触发语句是insert。为true否则为false
2、upodating ，如果是Update、为true
3、deleting。如果是delete、为true


create or replace trigger te_students_log
after insert or update or delete
on students
begin 
	if updating then
		insert into students_log values(user,'update',sysdate);
	end if;
	if inserting then
		insert into students_log values(user,'insert',sysdate);
	end if;
	if deleting then
		insert into students_log values(user,'delete',sysdate);
	end if;
end;

记录用户对数据表的操作日志

如果提交所有数据修改，那么表students_log的数据也将提交；而回滚表students的数据操作，那么触发器对students_log所做的数据操作也回滚

语句触发器的作用级别为表，即无论触发动作影响到多少记录都执行一次触发器

行触发器

对于表students的修改，需要记录其修改前的数据，即保存历史数据的需求，此时使用行触发器

创建和使用行触发器

行触发器的创建语法与语句触发器基本相同。只是需要为其添加for each row 选项。该选项正是行触发器的标志。

create or replace trigger tr_students_history
before update or delete 
on students
for each row
begin
	insert into students_history values(:old.students_id,:old.student_name,:old.students_age,:old.status,sysdate);
end	

:old是一个行类型变量，通过该变量可以引用行的各列。：old变量不能应用于insert操作类型的触发器

行触发器的变量引用

与：old变量对应的是：new 变量，：new用于引用新记录

触发器变量            insert   update   delete
：old                 不可用    可用      可用
：new                 可用      可用     不可用

create or replace trigger tr_students_upper
before update or insert
on students
for each row
begin
	:new.status :=upper(:new.status);
end;

在操作之前将新纪录的status列转换成大写。其触发时机不能是after

注意：

无论：old还是：new。改变其值就是改变了内存中记录的对应列值，无论真实的dml语句是否涉及被更新的列，这些更改都会应用到数据表的修改之中

create or replace trigger tr_students_upper
before update or insert
on students
for each row
begin
	：new.student_age := 0;
	:new.status :=upper(:new.status);
end;


update students set status='new';

即使Update语句没有涉及到列student_age。但是Oracle仍然将其更新为0

修改：old引用的值是无效动作，会报错。

变量引用与referencing

可以在触发器声明时利用referencing 关键字指定变量以代替：old与：new引用

create or replace trigger trigger_students_insert
before insert
on students
referencing new as new_value
for each row
begin
	declare max_id number;
	begin
		select max(student_id) into max_id from students;
		:new_value.students_id :=max_id+1;
	end;
end;

多个触发器的执行顺序

当某个动作激活了多个触发器时，这些触发器将按照以下顺序进行触发：高级别>低级别。表触发器先于行触发器。如果级别相同，创建时间晚的先于创建时间早的触发器

触发器的条件限制

create or replace trigger tr_employee_cxl
before update
on tmp_employees
for each row
when (old.emplayee_age<18)
begin
	:new.status := 'cxl';
end;		

when (old.emplayee_age<18)置于when (old.emplayee_age<18)之后，表示只有行满足employee_age小于18时，触发器才会触发。注意：限制条件必须用小括号扩起来，引用记录使用old而非：old

instead of 触发器

instead Of 触发器则用于代替触发动作，例如：insert动作的触发器不再进行insert动作，而是转而执行触发器动作

创建和使用instead Of 触发器

create or replace trigger tr_total_salary
instead of update 
on vw_total_salary
begin
	declare differ number;
	months number;
	begin
		select count(s.month) into months from salary s where s.employee_id=:old.employee_id;
		differ := (:new.total_salary-:old.total-salary)/months;
		update salary set salary=salary+differ where employee_id=:old.employee_id;
	end;
end;

起代替动作Update

instead Of 触发器中的变量引用

instead Of 触发器的触发动作包括了Update、insert、delete。针对每一种动作的每行数据，instead Of触发器都会执行一次。因此，instead Of触发器是行级触发器。但无须显示声明for each row。另外
，instead Of触发器虽可以直接引用原/新数据，但不能改变这些引用的值。

instead Of触发器用于代替原动作，因此，原动作将不会被执行。对于Update来说，一旦new引用的值进行了修改，Oracle会尝试将这种修改反映到数据库中–即执行真正的Update动作。而这恰恰违背了instead Of触发器的初衷。

系统事件与用户事件触发器

系统事件是指数据库级别的动作所触发的事件。这些事件主要包括数据库启动、数据库关闭、系统错误等。针对系统事件所建立的触发器称为系统事件触发器。

用户事件是相对于用户所执行的表（视图）等Dml操作而言的。常见的用户事件包括create事件、truncate事件、drop事件、alter事件、commit事件、rollback事件。针对用户事件所建立的触发器称为用户事件触发器。

系统事件与用户事件触发器并非常用触发器。

系统事件触发器

记录数据库启动时的时间

create or replace trigger tr_db_log
after startup
on database
begin
	insert into db_log values(user,'startup',sysdate);
end;

无需指定数据库名称，此时的数据库即为触发器所在的数据库

数据库关闭事件创建触发器

create or replace trigger tr_db_shutdown
befor shutdown
on database
begin
	insert into db_log values(user,'shutdown',sysdate);
end;

触发时机不能是数据库启动之前和数据库关闭之后

用户事件触发器

一个用户可以有数据库中的多个对象，例如一个用户拥有多个数据表和视图。用户事件触发器的作用对象不是单个对象，而是用户所拥有的所有对象的集合，即我们常说的schema。

以下动作的before和after时机均可创建触发器

create创建对象
alter修改对象属性
drop删除对象
analyze分析数据表的统计信息，以供优化器使用
associate statistics关联统计信息
disassociate statistics取消统计信息的关联
audit开启对象或系统上的审计功能，以便记录和跟踪用户操作
noaudit关闭对象或系统上的审计功能
comment为表或列添加注释。这些注释信息可以通过数据字典获取
grant为数据库用户授予权限或角色
revoke收回数据库用户的权限或角色
rename重命名数据库中的对象
truncate删除表中所有记录，并且不能回滚

以下事件只有before触发器

logoff用户退出数据库

以下事件只有after触发器

suspend当程序运行出现错误，并导致程序挂起


create or replace trigger tr_truncate_table
after truncate
on system.schema
begin
	insert into truncate_log values(ora_dict_obj_name,user,sysdate);
end;

on system.schema指定触发器的作用对象是用户system的所有对象；ora_dict_obj_name指定被删除对象的对象名称

触发器的相关操作

禁用触发器-disable

alter trigger trigger_name disable

启用触发器-enable

alter trigger trigger_name enable

在数据字典中查看触发器信息

在Oracle数据库中，与触发器相关的视图包括user_objects和user-triggers

select object_name,object_type,status from user_objects where lower(object_name) ='tr_students_upper';

select * from user_triggers

oracle 所允许的触发器级联（一个触发器执行会激活另一个触发器…）数据为35，超过35会报错。由于触发器级联的执行轨迹不易跟踪，因此在开发时应尽量避免触发器级联的出现