oracle创建和使用触发器

触发器：类似于AOP（面向切面编程）中的拦截器；不能传递参数，输出参数，也不能显示调用，只有满足触发器条件时会由Oracle自动调用。
触发器分类：
  语句级触发器；DML操作 insert delete update select
  行级触发器；
  系统事件触发器；数据库的关闭 启动

  用户事件触发器；DDL操作 drop alter create

--创建员工表t_employeecreate 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_salarycreate 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默认0create 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_employeeinsert 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 insertedSQL> 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 ACTupdate t_employee set status='act' where employee_id=11;--结果EMPLOYEE_ID EMPLOYEE_NAME         WORK_YEAR STATUS----------- -------------------- ---------- ----------         11 张静                          0 XXX【3】为单个事件多个触发器按照触发器的创建时间，同一事件按序触发不同的触发器前边创建的触发器：tr_before_insert_employee限制工龄为0tr_insert_update_employee限制status的字母为大写--测试多个触发器 如果触发器触发成功会将下面的work_year改为0，status改为大写ACTinsert 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的值改为ACTcreate 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