oracle创建和使用触发器

触发器：类似于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