触发器：类似于AOP（面向切面编程）中的拦截器；不能传递参数，输出参数，也不能显示调用，只有满足触发器条件时会由Oracle自动调用。
触发器分类：
语句级触发器；DML操作 insert delete update select
行级触发器；
系统事件触发器；数据库的关闭 启动
用户事件触发器；DDL操作 drop alter create
--创建员工表t_employee
create table t_employee(
employee_id number not null primary key,
employee_name varchar2(20),
work_year number,
status varchar2(10)
);
select * from t_employee;
--t_employee插入多条记录
insert all
into t_employee(employee_id,employee_name,work_year,status) values(1,'金瑞',5,'ACT')
into t_employee(employee_id,employee_name,work_year,status) values(2,'钟君',5,'ACT')
into t_employee(employee_id,employee_name,work_year,status) values(3,'王山',5,'ACT')
into t_employee(employee_id,employee_name,work_year,status) values(4,'刘迪',4,'ACT')
into t_employee(employee_id,employee_name,work_year,status) values(5,'钟会',3,'ACT')
into t_employee(employee_id,employee_name,work_year,status) values(6,'张玉',3,'ACT')
into t_employee(employee_id,employee_name,work_year,status) values(7,'柳青',3,'ACT')
into t_employee(employee_id,employee_name,work_year,status) values(8,'胡东',3,'ACT')
into t_employee(employee_id,employee_name,work_year,status) values(9,'商乾',3,'ACT')
into t_employee(employee_id,employee_name,work_year,status) values(10,'王蒙',1,'ACT')
select * from dual;
--创建工资表t_salary
create table t_salary(
salary_id number not null primary key,
employee_id number,
month varchar2(10),
salary number
);
select * from t_salary;
--t_salary插入多条记录
insert all
into t_salary(salary_id,employee_id,month,salary) values(1,1,'1月',8000)
into t_salary(salary_id,employee_id,month,salary) values(2,2,'1月',7000)
into t_salary(salary_id,employee_id,month,salary) values(3,3,'1月',7000)
into t_salary(salary_id,employee_id,month,salary) values(4,4,'1月',7000)
into t_salary(salary_id,employee_id,month,salary) values(5,5,'1月',6000)
into t_salary(salary_id,employee_id,month,salary) values(6,6,'1月',5500)
into t_salary(salary_id,employee_id,month,salary) values(7,7,'1月',5000)
into t_salary(salary_id,employee_id,month,salary) values(8,8,'1月',4000)
into t_salary(salary_id,employee_id,month,salary) values(9,9,'1月',4000)
into t_salary(salary_id,employee_id,month,salary) values(10,10,'1月',3000)
into t_salary(salary_id,employee_id,month,salary) values(11,1,'2月',8000)
into t_salary(salary_id,employee_id,month,salary) values(12,2,'2月',7000)
into t_salary(salary_id,employee_id,month,salary) values(13,3,'2月',7000)
into t_salary(salary_id,employee_id,month,salary) values(14,4,'2月',7000)
into t_salary(salary_id,employee_id,month,salary) values(15,5,'2月',6000)
into t_salary(salary_id,employee_id,month,salary) values(16,6,'2月',5500)
into t_salary(salary_id,employee_id,month,salary) values(17,7,'2月',5000)
into t_salary(salary_id,employee_id,month,salary) values(18,8,'2月',4000)
into t_salary(salary_id,employee_id,month,salary) values(19,9,'2月',4000)
into t_salary(salary_id,employee_id,month,salary) values(20,10,'2月',3000)
into t_salary(salary_id,employee_id,month,salary) values(21,1,'3月',8000)
into t_salary(salary_id,employee_id,month,salary) values(22,2,'3月',7000)
into t_salary(salary_id,employee_id,month,salary) values(23,3,'3月',7000)
into t_salary(salary_id,employee_id,month,salary) values(24,4,'3月',7000)
into t_salary(salary_id,employee_id,month,salary) values(25,5,'3月',6000)
into t_salary(salary_id,employee_id,month,salary) values(26,6,'3月',5500)
into t_salary(salary_id,employee_id,month,salary) values(27,7,'3月',5000)
into t_salary(salary_id,employee_id,month,salary) values(28,8,'3月',4000)
into t_salary(salary_id,employee_id,month,salary) values(29,9,'3月',4000)
into t_salary(salary_id,employee_id,month,salary) values(30,10,'3月',3000)
select * from dual;
【1】为单个事件定义触发器
--创建触发器：为了防止员工表中插入新数据work_year被改动，创建触发器在插入新数据时使得work_year默认0
create or replace trigger tr_before_insert_employee
before insert
on t_employee
for each row
begin
:new.work_year:=0;
end;
--查看创建的触发器信息
select * from user_objects where object_type='TRIGGER' and object_name='TR_BEFORE_INSERT_EMPLOYEE';
--测试触发器tr_before_insert_employee
insert into t_employee(employee_id,employee_name,work_year,status) values(11,'张静',5,'ACT');
--结果
SQL> insert into t_employee(employee_id,employee_name,work_year,status) values(11,'张静',5,'ACT');
1 row inserted
SQL> select * from t_employee where employee_id=11;
EMPLOYEE_ID EMPLOYEE_NAME WORK_YEAR STATUS
----------- --------------- ---------- ----------
11 张静 0 ACT
--演示：new只能用于行级触发器
create or replace trigger tr_before_insert_employee
before insert
on t_employee
--for each row
begin
:new.work_year:=0;
end;
--报错信息
ORA-04082: NEW 或 OLD 引用不允许在表级触发器中
--演示利用表级触发器限制用户操作
create or replace trigger tr_before_insert_employee
before insert
on t_employee
begin
if user!='ADMIN' then
raise_application_error('-20001','权限不足','不能向表中插入数据');
end if;
end;
drop trigger tr_before_insert_employee;
--测试限制触发器,以下DML操作会报错
insert into t_employee(employee_id,employee_name,work_year,status) values(11,'张静',5,'ACT');
【2】为多个事件定义触发器
--演示为多个事件创建触发器：将insert or update的status字段改为大写形式
create or replace trigger tr_insert_update_employee
before insert or update
on t_employee
for each row
begin
:new.status:=upper(:new.status);
end;
--测试上述触发器
insert into t_employee(employee_id,employee_name,work_year,status) values(11,'张静',5,'act');
--结果
EMPLOYEE_ID EMPLOYEE_NAME WORK_YEAR STATUS
----------- -------------------- ---------- ----------
11 张静 0 ACT
update t_employee set status='act' where employee_id=11;
--结果
EMPLOYEE_ID EMPLOYEE_NAME WORK_YEAR STATUS
----------- -------------------- ---------- ----------
11 张静 0 XXX
【3】为单个事件多个触发器
按照触发器的创建时间，同一事件按序触发不同的触发器
前边创建的触发器：
tr_before_insert_employee限制工龄为0
tr_insert_update_employee限制status的字母为大写
--测试多个触发器 如果触发器触发成功会将下面的work_year改为0，status改为大写ACT
insert into t_employee(employee_id,employee_name,work_year,status) values(12,'王静',5,'act');
--结果：符合预期值
EMPLOYEE_ID EMPLOYEE_NAME WORK_YEAR STATUS
----------- -------------------- ---------- ----------
12 王静 0 ACT
【4】创建触发器通过条件进行触发
--先删除之前的触发器
select * from user_objects where object_type='TRIGGER';
drop trigger TR_INSERT_UPDATE_EMPLOYEE;
drop trigger TR_BEFORE_INSERT_EMPLOYEE;
--创建触发器，注意：old和new在触发器的描述语句中使用，:old和:new在触发器的操作语句中使用
--如果work_year大于0，则把status的值改为ACT
create or replace trigger tr_update_employee
before update
on t_employee
for each row
when (old.status='CXF' and old.work_year>0)
begin
:new.status:='ACF';
end;
--测试触发器
update t_employee set employee_id=employee_id;--不会更改表内容，但会触发触发器
--结果
EMPLOYEE_ID EMPLOYEE_NAME WORK_YEAR STATUS
----------- -------------------- ---------- ----------
1 金瑞 5 ACT
2 钟君 5 ACT
3 王山 5 ACT
4 刘迪 4 ACT
5 钟会 3 ACF
6 张玉 3 ACF
7 柳青 3 ACF
8 胡东 3 ACF
9 商乾 3 ACT
10 王蒙 1 ACT
12 王静 0 ACT