程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> 觸發器，oracle觸發器

觸發器，oracle觸發器

編輯：Oracle教程

觸發器，oracle觸發器

--說明
--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 ;