触发器

目录

二、INSTEAD_OF触发器

三、系统触发器

四、删除触发器  修改触发器状态 关闭打开

--说明
--1.事件发生之前（BEFORE）事件发生之后(AFTER)
--2.触发条件子句WHEN
--3.语句级(STATEMENT)触发器和行级(ROW)触发器
--3.1 STATEMENT：是指当某触发事件发生时，该触发器只执行一次；
--3.2 ROW：是指当某触发事件发生时，对受到该操作影响的每一行数据，触发器都单独执行一次。
--4.在触发器的执行部分只能用DML语句（SELECT、INSERT、UPDATE、DELETE），不能使用DDL语句（CREATE、ALTER、DROP）。

一、DML触发器

--创建一个和emp表结构 相同的空表 记录删除日志
create table emp_del as select * from emp where 1=2;
--1.创建DML触发器[行级 删除 触发器]
create or replace trigger tr_del_emp
  before delete --指定为删除是触发
on emp
  for each row --说明 行级触发
begin
  insert into emp_del (deptno, empno) values (:old.deptno, :old.empno);
end;
--
delete emp t where t.empno='7654';
--注意：:NEW 修饰符访问操作完成后列的值     :OLD 修饰符访问操作完成前列的值

--2.限制DML触发器
create or replace trigger tr_dept_time
  before insert or delete or update on dept
begin
  if (true) then--这里可以写自定义条件
    RAISE_APPLICATION_ERROR(-20001, TO_CHAR(sysdate,'DAY')||'不能修改dept表');  --自定义异常消息
  end if;
end;

delete dept t where t.deptno='10';

--3修改字段ename, job 和删除 特定条件 DML触发器
CREATE OR REPLACE TRIGGER tr_emp_sal_comm
  BEFORE UPDATE OF ename, job OR DELETE ON emp  --修改字段ename, job 和删除 触发器
  FOR EACH ROW
  when (old.empno = '7499') --when 子句中old 和new 不能加":",在PL/SQL块语句中 必须加上:new :old 。
BEGIN
  CASE
    WHEN UPDATING('ename') THEN
      IF :new.ename != :old.ename THEN
        /*:new.ename != :old.ename  这里可以用新值和旧值做逻辑判断 注意值为空时 条件恒为false*/
        RAISE_APPLICATION_ERROR(-20001, 'enpno为7369 的ename 不能修改');
      elsif :new.ename = :old.ename THEN
        RAISE_APPLICATION_ERROR(-20001, 'ename 不能修改');
      else
        dbms_output.put_line(:new.ename || ' ' || :old.ename);
        RAISE_APPLICATION_ERROR(-20001, 'ename 其他情况不能修改');
      END IF;
    WHEN UPDATING('job') THEN
      IF :NEW.job != :old.job THEN
        RAISE_APPLICATION_ERROR(-20002, 'enpno为7369 的job 不能修改');
      END IF;
    WHEN DELETING THEN
      RAISE_APPLICATION_ERROR(-20003, 'enpno为7369 不能删除');
  END CASE;
END;

--7499  7566
select * from emp t where t.empno = 7499;
delete emp t where t.empno = 7499;
update emp t set t.job='12223' where t.empno='7499';
update emp t set t.ename='123' where t.empno='7499';

二、INSTEAD_OF触发器

--创建视图
create or replace view my_ceshi_view 
as
 select t.empno  from emp t group by t.empno;
--创建INSTEAD_OF触发器 删除 
create or replace trigger del_ceshi_view
  instead of delete on my_ceshi_view
  for each row
begin
  delete from emp where empno = :old.empno;
end;

--
delete my_ceshi_view t where  t.EMPNO='7698';

三、系统触发器

--创建用于记录事件用的表
CREATE TABLE ddl_event
(crt_date timestamp PRIMARY KEY,
 event_name VARCHAR2(20), 
 user_name VARCHAR2(10),
 obj_type VARCHAR2(20),
 obj_name VARCHAR2(20));

--创建系统事件DDL触发器
CREATE OR REPLACE TRIGGER tr_ddl
  AFTER DDL ON SCHEMA
BEGIN
  INSERT INTO ddl_event
  VALUES
    (systimestamp,
     ora_sysevent,
     ora_login_user,
     ora_dict_obj_type,
     ora_dict_obj_name);
END tr_ddl;

create table emp_bak as select * from emp;
select * from ddl_event;

四、删除触发器  修改触发器状态 关闭打开

--删除触发器  修改触发器状态 关闭打开
DROP TRIGGER trigger_name;
ALTER TRIGGER trigger_name [DISABLE | ENABLE ]; --有效状态(ENABLE) --无效状态(DISABLE)
ALTER TRIGGER tr_del_emp DISABLE ;