觸發器二(學習筆記)，觸發器學習筆記

觸發器二(學習筆記)，觸發器學習筆記

DML觸發器(語句觸發器)

由DML語句進行觸發，當用戶執行了INSERT,UPDATE,DELETE操作時就會觸發操作

示例一、只有在每個月的10日才允許辦理，新員工入職與離職，其他時間不允許增加和刪除員工數據

--建立表
CREATE TABLE myemp AS SELECT * FROM emp;

--創建觸發器
create or replace trigger changemyemp_trigger
  before INSERT OR DELETE on myemp  
declare
  v_curdate          Varchar2(20);
BEGIN
  SELECT to_char(SYSDATE,'dd') INTO v_curdate FROM dual;
  IF trim(v_curdate)<>'10' THEN
    Raise_application_error(-20003,'在每個月的10號才允許辦理入職和離職手續');
    END IF;
end changemyemp_trigger;

--向表中增加或者刪除數據
DECLARE
BEGIN
 -- INSERT INTO myemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES(8888,'Test','CLERK',7369,SYSDATE,8000,NULL,10);
  DELETE FROM myemp WHERE empno=7369;
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line(SQLERRM);
END;

如果日期不對會提示：

ORA-20003: 在每個月的10號才允許辦理入職和離職手續

示例二、周末及每天下班時間（每天9：00以前，18：00以後）不允許更新myemp表

-創建觸發器
create or replace trigger changemyemp_trigger
  before INSERT OR DELETE on myemp  

declare
  v_curhour         Varchar2(20);
  v_week             VARCHAR2(20);
BEGIN
  SELECT to_char(SYSDATE,'day'),to_char(SYSDATE,'hh24') INTO v_week,v_curhour FROM dual;
  IF trim(v_week) IN('星期六','星期日')  THEN
    Raise_application_error(-20003,'周末不允許更新myemp表');
    ELSIF TRIM(v_curhour)<'9'OR TRIM(v_curhour)>'18' THEN
      Raise_application_error(-20004,'在下班時間不允許更新myemp表');
    END IF;
end changemyemp_trigger;

--向表中增加或者刪除數據
DECLARE
BEGIN
 -- INSERT INTO myemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES(8888,'Test','CLERK',7369,SYSDATE,8000,NULL,10);
  DELETE FROM myemp WHERE empno=7369;
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line(SQLERRM);
END;

結果如果是周末：

ORA-20003: 周末不允許更新myemp表
ORA-06512: 在 "TESTS.CHANGEMYEMP_TRIGGER", line 7
ORA-04088: 觸發器 'TESTS.CHANGEMYEMP_TRIGGER' 執行過程中出錯

平時下班時間:

ORA-20004: 在下班時間不允許更新myemp表
ORA-06512: 在 "TESTS.CHANGEMYEMP_TRIGGER", line 9
ORA-04088: 觸發器 'TESTS.CHANGEMYEMP_TRIGGER' 執行過程中出錯

示例三、每一個員工都在根基本工資收入繳稅，2000以下3%，2000~5000，8%，5000以上10%，要求建立一張新的表來存放，員工編號，姓名，工資傭金，上繳的稅，並且每次在修改員工表中的SAL和COMM字段後自動更新記錄

-創建myemp_tax表
CREATE TABLE myemp_tax(
         empno  NUMBER(4),
         ename  VARCHAR2(10),
         sal    NUMBER(7,2),
         comm   NUMBER(7,2),
         tax    NUMBER(7,2),
         CONSTRAINT pk_myempno  PRIMARY KEY(empno),
         CONSTRAINT fk_myempno  FOREIGN KEY(empno) REFERENCES myemp(empno) ON DELETE CASCADE
);

--創建觸發器
create or replace trigger myemp_out
  after INSERT OR UPDATE OR DELETE on myemp  

declare
   PRAGMA AUTONOMOUS_TRANSACTION;                     --觸發器自主事務
   CURSOR cur_myemp IS SELECT * FROM myemp;        --定義游標找到每行的記錄
   v_sal             myemp.sal%TYPE;                --定義變量計算收入
   v_myemptax        myemp_tax.tax%TYPE;            --稅收
   v_myemp           myemp%ROWTYPE;
BEGIN
   DELETE FROM myemp_tax;             --清空myemp_tax表;
   FOR v_myemp IN cur_myemp LOOP
     v_sal:=v_myemp.sal+nvl(v_myemp.comm,0);          --計算總工資
     IF v_sal<2000 THEN
       v_myemptax:=v_sal*0.03;                         --上繳稅3%
     ELSIF v_sal BETWEEN 2000 AND 5000 THEN
     v_myemptax:=v_sal*0.08;                         --上繳稅8%
     ELSIF v_sal>5000 THEN
     v_myemptax:=v_sal*0.1;                         --上繳稅10%
      END IF;
      INSERT INTO myemp_tax(empno,ename,sal,comm,tax)
      VALUES(v_myemp.empno,v_myemp.ename,v_myemp.sal,v_myemp.comm,v_myemptax);
   END LOOP;
  COMMIT;
end myemp_out;

--向myemp表中增加一條的記錄，然後查詢myemp_tax表
INSERT INTO myemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES(8898,'Test','CLERK',7369,SYSDATE,800,100,10);
SELECT * FROM myemp_tax;