Oracle触发器

触发器

• 触发器是当特定事件出现时自动执行的存储过程

• 特定事件可以是执行更新的DML语句和DDL语句

• 触发器不能被显式调用

• 触发器的功能：

    自动生成数据
    自定义复杂的安全权限
    提供审计和日志记录
    启用复杂的业务逻辑
  

触发器的组成部分：

触发器语句（事件）
 定义激活触发器的 DML 事件和 DDL 事件
 触发器限制
执行触发器的条件，该条件必须为真才能激活触发器
触发器操作（主体）
包含一些 SQL 语句和代码，它们在发出了触发器语句且触发限制的值为真时运行

• 语法：

CREATE [OR REPLACE] TRIGGER trigger_name
AFTER | BEFORE | INSTEAD OF
[INSERT] [[OR] UPDATE [OF column_list]] 
[[OR] DELETE]
ON table_or_view_name
[REFERENCING {OLD [AS] old / NEW [AS] new}]
[FOR EACH ROW]
[WHEN (condition)]
pl/sql_block;

例子：

--创建一个触发器，每删除student表一条记录触发一次，并且输出删除的记录编号
create or replace trigger student_trigger--触发器名称
after --后置触发器,before、instead of、after
delete --删除操作，insert、delete、update
on student--作用在student表上，还可以在视图上
for each row--每一行都触发，也可以在语句级上
declare 
begin
  --:old指删除的记录行对象，:new 指新增的行对象，只能用在行级触发器中，不能用在语句级触发器
  dbms_output.put_line('删除的记录编号：'||:old.id);
end;

--1、创建触发器，只有在周一到周五上午9点到下午5点之间才可以对表student进行增删改操作

--2、创建触发器，创建一个日志记录表student_op_log（主键id数字类型，操作类型op_type字符串类型，
--描述信息remark字符串类型，操作时间create_time当前系统时间），
--用来存放对student表增删改操作日志记录

触发器类型

行级触发器
每影响一行就触发一次（加for each row）
例子1：

--创建一个触发器，每删除student表一条记录触发一次，并且输出删除的记录编号
create or replace trigger student_trigger--触发器名称
after --后置触发器,before、instead of、after
delete --删除操作，insert、delete、update
on student--作用在student表上，还可以在视图上
for each row--每一行都触发，也可以在语句级上
declare 
begin
--:old指删除的记录行对象，:new 指新增的行对象，只能用在行级触发器中，不能用在语句级触发器
  dbms_output.put_line('删除的记录编号：'||:old.id);
end;

例子2：

--创建一个触发器 ，分别在新增，修改，删除student表数据触发一次
create or replace trigger test_trigger
after
insert or update or delete
on student
for each row
declare

begin
  if inserting then
    dbms_output.put_line('新增操作');
    dbms_output.put_line('编号：'||:new.id||',姓名：'||:new.name);
    elsif updating then 
    dbms_output.put_line('修改操作');
    dbms_output.put_line('旧密码：'||:old.password||',新密码：'||:new.password);
    elsif deleting then
    dbms_output.put_line('删除操作');
    dbms_output.put_line('编号：'||:old.id);    
    else 
    dbms_output.put_line('其他操作');
  end if;
end;

例子3：

--创建前置触发器，一般用作校验
create or replace trigger check_trigger
before
insert or update or delete
on student
for each row
  declare
  
  begin
  if inserting then 
    if :new.sex not in(0,1) then
      raise_application_error(-20001,'性别只能为0或1');
    end if;
    dbms_output.put_line('编号：'||:new.id||',姓名：'||:new.name);
    elsif updating then
    if :new.age not between 18 and 120 then
      raise_application_error(-20002,'年龄范围错误，只能在18-120之间');
      end if;
    dbms_output.put_line('旧年龄：'||:old.age||',新年龄：'||:new.age);      
  
    elsif deleting then
    if :old.id  between 100 and 200 then
      raise_application_error(-20003,'删除错误，不能删除编号在100-200之间的记录');
      end if;
      dbms_output.put_line('删除编号：'||:old.id);   
      else 
      dbms_output.put_line('其他操作');   
        end if;
         end;

语句级触发器
影响多行数据只触发一次
INSTEAD OF 触发器
语法：

SQL> CREATE OR REPLACE TRIGGER upd_ord_view
          INSTEAD OF UPDATE ON ord_view
          FOR EACH ROW
          BEGIN
     		UPDATE order_master
          SET vencode=:NEW.vencode 
		WHERE orderno = :NEW.orderno;
		DBMS_OUTPUT.PUT_LINE(‘已激活触发器');
          END;

----instead of 触发器
--创建视图
create or replace view v_emp_dept
as
select e.employee_id,e.first_name,e.salary,d.department_id,d.department_name
from emp e,dept d
where e.department_id=d.department_id(+)
order by e.employee_id;
--查询视图,不能直接对视图数据进行操作
select * from v_emp_dept;
--添加视图,不能直接对视图添加数据
insert into v_emp_dept(employee_id,first_name,salary,department_id,department_name) values(301,'张三',10000,500,'软件');
--修改视图，不能直接对视图数据进行修改
update v_emp_dept v set v.department_name='lala' where v.employee_id=301; 
--删除视图数据，不能直接对视图数据进行删除
delete v_emp_dept v where v.employee_id=301;
--创建触发器，对视图进行增删改
--原理：实际上是通过程序逻辑对真实表数据进行增删改，只是对外部调用者透明，感觉好像是直接可以对视图进行操作。
create or replace trigger view_trigger
instead of --只能用在视图上
insert or update or delete 
on v_emp_dept--视图名称
for each row
  begin
    if inserting then 
      insert into emp(employee_id,first_name,salary,department_id)
      values(:new.employee_id,:new.first_name,:new.salary, :new.department_id);
      insert into dept(department_id,department_name) values(:new.department_id,:new.department_name);
      dbms_output.put_line('添加成功');
    elsif updating then
    update emp e
       set e.first_name    = :new.first_name,
           e.salary        = :new.salary,
           e.department_id = :new.department_id
     where e.department_id = :new.department_id;
     update dept d
        set d.department_name = :new.department_name
      where d.department_id = :new.department_id;
            dbms_output.put_line('修改成功');
    elsif deleting then
      delete emp e where e.employee_id=:old.employee_id;
            dbms_output.put_line('删除成功');
    end if;
  end;

模式触发器
语法：

SQL> CREATE OR REPLACE TRIGGER log_drop_obj
AFTER DROP ON SCHEMA
BEGIN
  INSERT INTO dropped_obj
  VALUES( ORA_DICT_OBJ_NAME, 
          ORA_DICT_OBJ_TYPE, SYSDATE);
END;

例子：

--创建用户模式级触发器,对对象结构进行操作ddl语句
create or replace trigger schema_trigger
after
drop or create or alter--支持创建、修改、删除
on schema--用户模式级别
begin
  dbms_output.put_line('对象拥有者:'||ora_dict_obj_owner);
  dbms_output.put_line('当前登录用户:'||ora_login_user);
  dbms_output.put_line('对象名称：'||ora_dict_obj_name);
  dbms_output.put_line('对象类型'||ora_dict_obj_type);
  dbms_output.put_line('数据库实例名称：'||ora_database_name);  
  dbms_output.put_line('数据库ip地址'||ora_client_ip_address);
  dbms_output.put_line('创建时间'||to_char(sysdate,'yyyy-MM-dd hh24:mi:ss'));

end;
--测试触发器
drop table tex;
create table tex as select * from student where 1=2;

启用、禁用和删除触发器
禁用触发器语法：alter trigger 触发器名 disable;
启用触发器：SQL> ALTER TRIGGER 触发器名 ENABLE;
删除触发器：SQL> DROP TRIGGER 触发器名;

--禁用触发器        
  alter trigger student_trigger disable;
  alter trigger test_trigger disable;