一.语句级触发器:它是表级触发器,在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_log
before insert or update
on t_employee
begin
insert into employee_log values(user,sysdate);
end;
--向t_employee表中插入数据触发语句级触发器
insert into t_employee values(11,'李静',6,'ACT');
--查看employe_log
select * from employee_log;
--结果
UPDATE_BY UPDATE_AT
-------------------- -----------
SCOTT 2017/9/4 22
--修改t_employee表中数据触发语句级触发器
update t_employee set work_year=work_year+1;
--查看employe_log
select * from employee_log;
--结果
UPDATE_BY UPDATE_AT
-------------------- -----------
SCOTT 2017/9/4 22
SCOTT 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_log
before insert or update
on t_employee
begin
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_log
select * 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_log
select * from employee_log;
--结果
UPDATE_BY UPDATE_AT ACTION
-------------------- ----------- ----------
SCOTT 2017/9/4 22
SCOTT 2017/9/4 22
SCOTT 2017/9/4 23 INSERT
SCOTT 2017/9/4 23 UPDATE
综上,日志表会记录t_employee表的更新操作。
--before,after关键字
根据不同的场景,触发器的触发时机不同。
before:在对用户的权限进行控制时,只有有权限的才能执行相应的DML或者DDL,DCL,TCL操作。
after:适合对日志等进行记录,如果用户在执行sql发生了错误,回滚事务,相当于没有实际操作,所以在成功执行后再进行日志记录。
--所以,上面的日志触发器应改为after
create or replace trigger tr_employee_log
after insert or update
on t_employee
begin
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_log
after insert or update
on t_salary
begin
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_log
before insert or update
on t_employee
begin
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 where
end;
三.行级触发器:它是表级触发器,在insert,delete,update,select的DML操作中只会被触发一次insert可以使用:new
delete可以使用:old
update可以使用:new和:old
--创建行级触发器
create or replace trigger tr_before_insert_employee
before insert
on t_employee
for each row
begin
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_history
after update or delete
on t_employee
for each row
begin
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_salary
as
select 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_salary
instead of update
on 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_startup
after startup
on database
begin
null;
end;
create or replace trigger tr_database_startup
before shutdown
on database
begin
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时,余额表进行相应的变化,支出表同理
--收入表income
Name Type Nullable Default Comments
------ ------ -------- ------- --------
ID NUMBER Y
INCOME NUMBER Y 收入
--支出表payout
Name Type Nullable Default Comments
------ ------ -------- ------- --------
ID NUMBER Y
PAYOUT NUMBER Y 支出
--余额表balance
Name Type Nullable Default Comments
------- ------ -------- ------- --------
BALANCE NUMBER Y 余额
--为income表创建触发器
create or replace trigger tr_income
after insert or delete or update on income
for each row
begin
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_payout
after insert or delete or update on payout
for each row
begin
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;