oracle触发器分类

一.语句级触发器：它是表级触发器，在insert,delete,update,select的DML操作中只会被触发一次

--【1】创建日志表：记录每次向表中更新数据的用户的时间create table employee_log(       update_by varchar2(20),       update_at date);select * from employee_log;--创建t_employee的语句级触发器create or replace trigger tr_employee_logbefore insert or updateon t_employeebegin insert into employee_log values(user,sysdate);end;--向t_employee表中插入数据触发语句级触发器insert into t_employee values(11,'李静',6,'ACT');--查看employe_logselect * from employee_log;--结果UPDATE_BY            UPDATE_AT-------------------- -----------SCOTT                2017/9/4 22--修改t_employee表中数据触发语句级触发器update t_employee set work_year=work_year+1;--查看employe_logselect * from employee_log;--结果UPDATE_BY            UPDATE_AT-------------------- -----------SCOTT                2017/9/4 22SCOTT                2017/9/4 22

综上，无论修改表中多少数据，语句级触发器只会触发一次。

【2】触发器谓词 insertint,deleting,updating

--为employe_log表添加action字段用来记录用户执行了什么操作alter table employee_log add (action varchar2(10));--修改tr_employee_log触发器create or replace trigger tr_employee_logbefore insert or updateon t_employeebegin  if inserting then     insert into employee_log values(user,sysdate,'INSERT');  end if;  if deleting then     insert into employee_log values(user,sysdate,'DELETE');  end if;  if updating then     insert into employee_log values(user,sysdate,'UPDATE');  end if;end;--向t_employee表中插入数据触发语句级触发器insert into t_employee values(13,'尼欧',6,'ACT');--查看employe_logselect * from employee_log;--结果UPDATE_BY            UPDATE_AT   ACTION-------------------- ----------- ----------SCOTT                2017/9/4 22 SCOTT                2017/9/4 22 SCOTT                2017/9/4 23 INSERT--修改t_employee表中数据触发语句级触发器update t_employee set work_year=work_year+1;--查看employe_logselect * from employee_log;--结果UPDATE_BY            UPDATE_AT   ACTION-------------------- ----------- ----------SCOTT                2017/9/4 22 SCOTT                2017/9/4 22 SCOTT                2017/9/4 23 INSERTSCOTT                2017/9/4 23 UPDATE

综上，日志表会记录t_employee表的更新操作。

--before,after关键字
根据不同的场景，触发器的触发时机不同。
before:在对用户的权限进行控制时，只有有权限的才能执行相应的DML或者DDL,DCL,TCL操作。
after:适合对日志等进行记录，如果用户在执行sql发生了错误，回滚事务，相当于没有实际操作，所以在成功执行后再进行日志记录。

--所以，上面的日志触发器应改为aftercreate or replace trigger tr_employee_logafter insert or updateon t_employeebegin  if inserting then     insert into employee_log values(user,sysdate,'INSERT');  end if;  if deleting then     insert into employee_log values(user,sysdate,'DELETE');  end if;  if updating then     insert into employee_log values(user,sysdate,'UPDATE');  end if;end;

二.触发器的级联
注意：对A表操作激活触发器1,触发器1会对B表进行操作，B表上又建立了触发器2,如果触发器2会对A表进行操作，那么就会产生死循环。

--示例create table salary_log(       update_by varchar2(20),       update_at date,       action varchar2(20));select * from salary_log;--------------------------------------create or replace trigger tr_salary_logafter insert or updateon t_salarybegin  if inserting then     insert into salary_log values(user,sysdate,'INSERT');  end if;  if deleting then     insert into salary_log values(user,sysdate,'DELETE');  end if;  if updating then     insert into salary_log values(user,sysdate,'UPDATE');  end if;end;--------------------------------------create or replace trigger tr_employee_logbefore insert or updateon t_employeebegin  if inserting then     insert into employee_log values(user,sysdate,'INSERT');  end if;  if deleting then     insert into employee_log values(user,sysdate,'DELETE');  end if;  if updating then     insert into employee_log values(user,sysdate,'UPDATE');  end if;  delete from t_salary whereend;

三.行级触发器：它是表级触发器，在insert,delete,update,select的DML操作中只会被触发一次

insert可以使用:new
delete可以使用:old

update可以使用:new和:old

--创建行级触发器create or replace trigger tr_before_insert_employeebefore inserton t_employeefor each rowbegin  select (max(employee_id)+1) into :new.employee_id from t_employee;  :new.status:=upper(:new.status);  :new.work_year:=0;end;--测试insert into t_employee values(0,'行级触发器',10,'act');--结果13 行级触发器                    0 ACT--测试:利用内嵌视图查找插入的数据select * from (select * from t_employee order by employee_id desc) where rownum=1;--结果13 行级触发器                    0 ACT--示例：使用行触发器记录日志create table t_employee_history(       employee_history_id number not null primary key,       employee_id number not null,       employee_name varchar2(10),       work_year number,       status varchar2(20),       action varchar2(20),       update_by varchar2(20),       update_at date);--删除之前t_employee表上的触发器select * from user_objects where object_type='TRIGGER'drop trigger tr_salary_log;drop trigger tr_employee_log;drop trigger tr_before_insert_employee;--为t_employee表创建新的触发器create or replace trigger tr_employee_historyafter update or deleteon t_employeefor each rowbegin  declare historyId number;  begin    select nvl(max(employee_history_id)+1,0) into historyId from t_employee_history;    if updating then      insert into t_employee_history values(historyId,:old.employee_id,:old.employee_name,:old.work_year,'插入数据',:old.status,user,sysdate);    end if;    if deleting then       insert into t_employee_history values(historyId,:old.employee_id,:old.employee_name,:old.work_year,'删除数据',:old.status,user,sysdate);    end if;  end;end;--测试行级触发器update t_employee set employee_name='日志触发器' where employee_id=13;update t_employee set employee_name='日志触发器' where employee_id=12;update t_employee set employee_name='日志触发器' where employee_id=11;delete from t_employee where employee_id=13;--结果select * from t_employee_history;E_HISTORY_ID EMPLOYEE_ID EMPLOYEE_NAME  WORK_YEAR STATUS               ACTION               UPDATE_BY            UPDATE_AT------------------- ----------- ------------- ---------- -------------------- -------------------- -------------------- -----------                  0          13 行级触发器             0 插入数据             ACT                  SCOTT                2017/9/5 22                  1          13 日志触发器             0 删除数据             ACT                  SCOTT                2017/9/5 22                  2          12 李思思                 6 插入数据             XXX                  SCOTT                2017/9/5 22                  3          11 李静                  11 插入数据             XXX                  SCOTT                2017/9/5 22

触发时机和瞬态，对于行级触发器而言

1.before触发器：插入单条记录会成功，批量插入记录会失败。
原因：因为before触发器，插入单条数据时，表的状态是确定的。如果批量插入数据，那么表的状态就会变成瞬态mutating，
Oracle不允许在表是瞬态的时候去访问触发器关联的表。
2.after触发器：插入单条记录或者批量插入记录都会失败。因为在触发after触发器之前表的状态并没有提交，所以表处于瞬态。

综上所述，如果表处于瞬态则是不允许触发器操作的。

instead of触发器：
语级触发器和行级触发器和触发动作是叠加执行的。
instead of触发器会替代触发语句转而执行触发器操作。

注意：
instead of触发器相当于行级触发器所以不用加for each row.
instead of触发器可以使用访问new引用，但不能更改引用的值。如： :new.salary=:new.salary+1000

--创建视图，统计员工的工资create or replace view view_salaryasselect e.employee_id,e.employee_name,sum(s.salary) total from t_salary s,t_employee e where s.employee_id=e.employee_id group by e.employee_id,e.employee_name;--创建instead of触发器替代update操作create or replace trigger tr_insteadOf_salaryinstead of updateon view_salary  begin    declare totalMonth number;    begin      select count(distinct(month)) into totalMonth from t_salary t where t.employee_id=:old.employee_id;      update t_salary set salary=salary+(:new.total-:old.total) /totalMonth where employee_id=:old.employee_id;    end;  end;--查看触发器状态select * from user_objects where object_type='TRIGGER'--该员工一季度的工资select * from view_salary where employee_id=10;--15000----先查看该员工的月工资select * from t_salary where employee_id=10;  --5000--测试instead of触发器update view_salary set total=15000 where employee_id=10;

三.系统事件触发器和用户事件触发器

1.系统事件触发器：数据库启动，数据库关闭，系统错误等

--示例create or replace trigger tr_database_startupafter startupon databasebegin  null;end;create or replace trigger tr_database_startupbefore shutdownon databasebegin  null;end;注意：startup的话不能写成before，因为数据库未启动是不能存储触发器的shutdown的话不能写成after，因为数据库未启动是不能存储触发器的

2.用户事件触发器：create,alter,drop,commit,rollback,truncate等,相对于DML操作而言

四.启用和禁用触发器

启用触发器：alter trigger tr_database_startup enable;
关闭触发器：alter trigger tr_database_startup disable;
查看触发器信息
select * from user_objects where object_type='TRIGGER';
select * from user_triggers;

--触发器实例--创建收入表，支出表，余额表，在其上创建触发器，当收入表insert,delete,update时，余额表进行相应的变化，支出表同理--收入表incomeName   Type   Nullable Default Comments ------ ------ -------- ------- -------- ID     NUMBER Y                         INCOME NUMBER Y                收入 --支出表payoutName   Type   Nullable Default Comments ------ ------ -------- ------- -------- ID     NUMBER Y                         PAYOUT NUMBER Y                支出--余额表balanceName    Type   Nullable Default Comments ------- ------ -------- ------- -------- BALANCE NUMBER Y                余额 --为income表创建触发器create or replace trigger tr_incomeafter insert or delete or update on incomefor each rowbegin  if inserting then     update balance set balance=balance+:new.income;  elsif deleting then     update balance set balance=balance-:new.income;  elsif updating then     update balance set balance=balance-:old.income+:new.income;  end if;end; --为payout表创建触发器create or replace trigger tr_payoutafter insert or delete or update on payoutfor each rowbegin  if inserting then     update balance set balance=balance-:new.payout;  elsif deleting then     update balance set balance=balance+:new.payout;  elsif updating then     update balance set balance=balance-:old.payout+:new.payout;  end if;end; --测试tr_income触发器insert into income values(2,10);commit;select * from balance;