触发器是特定事件出现的时候，自动执行的代码块。
类似于存储过程，但是用户不能直接调用他们
功能：
1、 允许/限制对表的修改
2、 自动生成派生列，比如自增字段
3、 强制数据一致性
4、 提供审计和日志记录
5、 防止无效的事务处理
6、 启用复杂的业务逻辑
create trigger biufer_empployees_department_id before
insert or update of department_id
on employees
referencing old as old_value new as new_value
for each row
when(new_value.department_id!=80)
begin
:new_value.commision_pct:=0;
end;
触发器的组成部分：
1、 触发器名称
2、 触发语句
3、 触发器限制
4、 触发操作
1、 触发器名称
create trigger biufer_employees_department_id
命名习惯：biufer（before insert update for each row）
employees 表名
department_id 列名
2、 触发语句
表或视图上的dml语句
ddl语句
数据库关闭或启动,startup shutdown 等等
before insert or
update of department_id
on employees
referencing old as old_value
new as new_value
for each row
1)规定了对employees表进行insert的时候 触发器会激活
2)对employees表的department_id列进行update的时候 触发器会激活
3、 触发器限制
when (new_value.department_id<>80 )
其中的new_value是代表更新之后的值
限制不是必须的
4、 触发操作
是触发器的主体
begin
:new_value.commission_pct :=0;
end;
主体很简单，就是将更新后的commission_pct列置为0
触发时机
在dml触发器中，根据触发时机不同，可以分为before和after,但是在触发过程中其顺序不同。
执行顺序为:
1.before触发器
2.约束检查
3.更新表
4.after触发器
Before触发器是在约束之前执行的
通常用于：
1.设置或修改更新或插入的列值
2.检查复杂的安全规则，如限制时间
3.增强商业应用规则
4.可以通过触发器的逻辑潜在的引发一个异常来拒绝触发语句，则相对有效，因为是在约束前执行
After触发器最后执行
一般用于：
1.用户信息的审计
2.导出数据的生成
3.远程数据的复制
触发器类型：
1、 语句触发器
2、 行触发器
3、 instead of 触发器
4、 系统条件触发器
5、 用户事件触发器
1)语句触发器
是在表上或者某些情况下的视图上执行的特定语句或者语句组上的触发器
能够与insert、update、delete或者组合上进行关联
但是无论使用什么样的组合，各个语句触发器都只会针对指定语句激活一次
比如，无论update多少行，也只会调用一次update语句触发器
ey:需要对在表上进行dml操作的用户进行安全检查，看是否具有合适的特权
create table foo(a number);
create trigger biud_foo
before insert or update or delete
on foo
begin
if user not in (‘donny’) then
raise_application_error(-20001, ‘you don’t have access to modify this table.’);
end if;
end;
ey:对修改表的时间、人物进行日志记录
1、 建立试验表
create table employees_copy as select *from emp;
2、 建立日志表
create table employees_log(
who varchar2(30),
when date);
3、 在employees_copy表上建立语句触发器，在触发器中填充employees_log 表
create or replace trigger biud_employee_copy
before insert or update or delete
on employees_copy
begin
insert into employees_log(who,when)
values( user, sysdate);
end;
4、 测试
update employees_copy set salary= salary*1.1;
select *from employess_log;
5、 确定是哪个语句起作用？
即是insert/update/delete中的哪一个触发了触发器？
可以在触发器中使用inserting / updating / deleting 条件谓词，作判断：
begin
if inserting then
-----
elsif updating then
-----
elsif deleting then
------
end if;
end;
if updating(‘col1’) or updating(‘col2’) then
------
end if;
a.修改日志表
alter table employees_log add (action varchar2(20));
b.修改触发器，以便记录语句类型
create or replace trigger biud_employee_copy
before insert or update or delete
on employees_copy
declare
l_action employees_log.action%type;
begin
if inserting then
l_action:=’insert’;
elsif updating then
l_action:=’update’;
elsif deleting then
l_action:=’delete’;
else
raise_application_error(-20001,’you should never ever get this error.’);
end if;
insert into employees_log(who,action,when)
values( user, l_action,sysdate);
end;
c.测试
update employees_copy set salary=50000 where employee_id = 12345;
select *from employees_log
;
2)行触发器
是指为受到影响的各个行激活的触发器，定义与语句触发器类似，有以下两个例外：
1、 定义语句中包含for each row子句
2、 在for each row触发器中，用户可以引用受到影响的行值。
create trigger biufer_empployees_department_id before
insert or update of department_id
on employees
referencing old as old_value new as new_value
for each row
when(new_value.department_id!=80)
begin
:new_value.commision_pct:=0;
end;
referencing 子句：
执行dml语句之前的值的默认名称是 :old ,之后的值是 :new
insert 操作只有:new
delete 操作只有 :old
update 操作两者都有
referencing子句只是将new 和old重命名为new_value和old_value，目的是避免混淆。
比如操作一个名为new的表时。
ey:为主健生成自增序列号
drop table foo;
create table foo(id number, data varchar2(20));
create sequence foo_seq;
create or replace trigger bifer_foo_id_pk
before insert on foo
for each row
begin
select foo_seq.nextval into :new.id from dual;
end;
语句与行级触发器的区别？
行级触发器 语句级触发器
被触发语句影响的每一行所触发 不管影响多少行，触发语句只执行一次
若触发事件对行没有产生更改影响，则不触发 即使触发事件不影响任何一行，触发器也触发
都可指定before,after 都可指定before,after
触发器执行体可以读取行数据 触发器执行体与行数据无关
只受当前触发事件影响 只受当前触发事件影响
After行触发器锁定数据行 不锁定数据行
同一表上触发器的优先级别
同一表上可以定义多个触发器，最多可以创建12种类型的触发器，但是其触发是有固定顺序的，其先后顺序为：
1.before语句级触发器
2.before行级触发器
3.after行级触发器
4.after语句级触发器
3)instead of 触发器更新视图
create or replace view v_emp_dept as
select empno, ename , hiredate, sal,deptno, dname from emp,dept
where emp.deptno=dept.deptno;
尝试往v_emp_dept表插入数据时只能通过替代触发器来完成
create or replace trigger tr_v
instead of insert on v_emp_dept
for each row
begin
insert into dept values(:new.deptno,:new.dname);
insert into emp (empno,ename,hiredate,sal) values(:new.empno,:new.ename,:new.hiredate,:new.sal);
end;
替代触发器的使用经验
替代触发器可用来操纵对视图的插入，修改和删除。
Before和after无法用于替代触发器
视图上的check约束对替代触发器无效，因此需要在触发器语句内加强相关约束
Dml触发器是在dml操作外运行的，而替代触发器则代替触发它的dml命令运行。本质上,替代触发器属于行级。
4)系统事件触发器
系统事件：数据库启动、关闭，服务器错误
create trigger ad_startup
after startup
on database
begin
-- do some stuff
end;
5)用户事件触发器
用户事件：用户登陆、注销，create / alter / drop / analyze / audit / grant / revoke / rename / truncate / logoff
ey:记录删除对象
a.日志表
create table droped_objects(
object_name varchar2(30),
object_type varchar2(30),
dropped_on date);
b.触发器
create or replace trigger log_drop_trigger
before drop on donny.schema
begin
insert into droped_objects
values(
ora_dict_obj_name, -- 与触发器相关的函数
ora_dict_obj_type,
sysdate);
end;
c.测试
create table drop_me(a number);
create view drop_me_view as select *from drop_me;
drop view drop_me_view;
drop table drop_me;
select *from droped_objects
禁用和启用触发器
alter trigger <trigger_name> disable;
alter trigger <trigger_name> enable;
事务处理：
在触发器中，不能使用commit / rollback
因为ddl语句具有隐式的commit，所以也不允许使用
触发器的限制
触发器的使用存在一些限制条件，需要引起高度重视！
1.create trigger语句文本字符长度不能超过32k，
如果触发器的逻辑编码超过60行，则最好将主要代码保存到存储过程。
使用时之需要从触发器中调用存储过程即可。
2.不要创建递归式触发器，否则，系统会因为触发器与表之间的循环操作而导致内存耗尽，系统崩溃。
3.触发器中不能使用数据库事务控制语句commit,rollback和savepoint
4.由触发器所调用的过程或者函数也不能使用数据库事务控制语句。
5.触发器中不能声明long,long raw类型变量，也不能在触发表的long 或long raw列上使用:new和:old.
6.触发器体内可以读取Lob类型的列值，但不能通过：new修改lob列的数据。
7.when条件子句只适用于行级触发器，一旦指定了when ，触发器只执行满足when子句条件的行
问题：
写一个ORACLE 触发器,主要是解决一个系统的管理员帐号经常被培训的人修改的问题.
功能是在Update 用户表时如果是修改的管理员,
则将管理员的密码改回到初始状态（假定初始密码是oldpassword)
,用户表是user(id ,password,job_title)
CREATE OR REPLACE TRIGGER before_update_password
before update of password
on new_users
for each row
when (new.jobtitle='admin')
declare
err exception;
begin
dbms_output.put_line('trigger active');
if :old.jobtitle='admin' then
raise err;
end if;
exception
when err then
raise_application_error(-20009,'pls do not change admin password');
when others then
dbms_output.put_line(sqlerrm);
end;
触发器与变异表
变异表:当前正在被DML语句更新的表，对于触发器而言，变异表就是在其上定义该触发器的那张表。
问题：将每一门课程的选修人数限制在2人，在students表上定义一个before insert or update行级触发器实现
students（Id,major)
create or replace trigger limitmajors
before insert or update
of major
on students
for each row
declare
v_maxstudents constant number:=2;
v_currentstudents number;
begin
select count(*) into v_currentstudents
from students where major=:new.major;
if(v_currentstudents+1>v_maxstudents) then
raise_application_error(-20001,‘too many students major in ‘||:new.major);
end if;
end;
创建测试环境
create table students(id number primary key,major varchar2(20));
insert into students values(1,'math');
insert into students values(2,'math');
insert into students values(3,'math');
用insert ….select 子句测试
create table students_copy as select * from students where 1=2;
insert into students_copy values(3,‘math’)；
insert into students
select * from students_copy;
用update测试
insert into students values(3,'english');
update students set major='math' where id=3;
修改思路如下：
将上面的触发器修改成2个触发器，行级与语句级触发器
1.在行级触发器中，可以记录 :new.major 的值，但不查询students表
2.查询在语句级触发器中实现
需要 保存一些全局性变量以在两个触发器间传递变量，可以用包来实现：
Create or replace package studentdata as
Type t_majors is table OF students.major%type
Index by BINARY_INTEGER;
Type t_IDs is table OF students.ID%type
Index by BINARY_INTEGER;
V_studentmajors t_majors;
V_studentids t_ids;
V_numentries binary_integer:=0;
End studentdata;
行级触发器
Create or replace trigger rlimitmajors
Before insert or update of major on students
For each row
Begin
Studentdata.v_numentries:=studentdata.v_numentries+1;
Studentdata.v_studentmajors(studentdata.v_numentries):=:new.major;
Studentdata.v_studentids(studentdata.v_numentries):=:new.id;
End rlimitmajors;
语句触发器
Create or replace trigger slimitmajors
after insert or update of major on students
declare
v_maxstudents constant number:=2;
v_currentstudents number;
v_studentid students.id%type;
v_major students.major%type;
begin
for v_loopindex in 1..studentdata.v_numentries loop
v_studentid :=studentdata.v_studentids(v_loopindex);
v_major:= studentdata.v_studentmajors(v_loopindex);
select count(*) into v_currentstudents
from students where major= v_major;
If V_currentstudents > V_maxstudents then
RAISE_APPLICATION_ERROR(-20000,’too many students in major ’||v.major);
End if;
End loop;
Studentdata.v_numentries:=0;
End slimitmajors;
说明
1.确保变量成为全局变量的唯一方法就是将它存放在某个包中。
2.注意用after语句级触发器中进行处理