Oracle觸發(fā)器trigger詳解

create or replace trigger insertStaffHint
 after insert on xgj_test 
 for each row
declare
 -- local variables here
begin

 dbms_output.put_line('新增員工成功');

end insertStaffHint;

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER }
{INSERT | DELETE | UPDATE [OF column [, column …]]}
[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]
ON [schema.]table_name | [schema.]view_name
[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
[FOR EACH ROW ]
[WHEN condition]
PL/SQL_BLOCK | CALL procedure_name;

BEFORE INSERT
BEFORE INSERT FOR EACH ROW
AFTER INSERT
AFTER INSERT FOR EACH ROW

BEFORE UPDATE
BEFORE UPDATE FOR EACH ROW
AFTER UPDATE
AFTER UPDATE FOR EACH ROW

BEFORE DELETE
BEFORE DELETE FOR EACH ROW
AFTER DELETE
AFTER DELETE FOR EACH ROW

/**
非工作時間（星球六 星期日， 非9點~18點的區(qū)間）
禁止寫入數(shù)據(jù)

首先要搞清楚： 觸發(fā)器的類型--語句級觸發(fā)器。
不管插入了多少條數(shù)據(jù)，沒有必要對每一行數(shù)據(jù)都進行校驗，只要不在這個時間段內，都不讓插入。
*/
create or replace trigger addStafffCheck
 before insert on xgj_test 

declare
 -- local variables here
begin

 if to_char(sysdate, 'day') in ('星期六', '星期日') or
 to_number(to_char(sysdate, 'hh24')) not between 9 and 18 then
 --禁止insert 
 raise_application_error(-20001,'非工作時間禁止插入數(shù)據(jù)');
 end if;
end addStafffCheck;

/**
漲后的薪水不能低于漲前的薪水

1 :old 和 :new 代表同一條記錄
2 :old 代表操作該行之前，這一行的值
 :new 代表操作該行之后，這一行的值
*/

create or replace trigger checkSalary
 before update 
 on xgj_test 
 for each row
declare
 -- local variables here 沒有變量聲明的話，declare可以省略
begin

 --- if 漲后的薪水  漲前的薪水 then 如何表示呢 ？
 if :new.sal  :old.sal then

 raise_application_error(-20002,'漲后的薪水:'|| :new.sal ||'小于漲前的薪水：'||:old.sal);
 end if;
end checkSalary;

create table xgj_record(info varchar2(256)) ;
create or replace trigger addrecord
 after update
 on xgj_test 
 for each row
declare
 -- local variables here
begin

 if :new.sal > 6000 then 
 insert into xgj_record values(:new.sal ||'-'|| :new.username ||'-'|| :new.job);
 end if;

end addrecord;

create table xgj_test_bak as select *　from xgj_test ;

create or replace trigger databack
 after update
 on xgj_test
 for each row 

begin

 update xgj_test_bak set sal = :new.sal where username = :new.username ;

end databack;

CREATE TABLE emp_his AS SELECT * FROM EMP WHERE 1=2;
CREATE OR REPLACE TRIGGER tr_del_emp
 BEFORE DELETE --指定觸發(fā)時機為刪除操作前觸發(fā)
 ON scott.emp
 FOR EACH ROW --說明創(chuàng)建的是行級觸發(fā)器
BEGIN
 --將修改前數(shù)據(jù)插入到日志記錄表 del_emp ,以供監(jiān)督使用。
 INSERT INTO emp_his(deptno , empno, ename , job ,mgr , sal , comm , hiredate )
 VALUES( :old.deptno, :old.empno, :old.ename , :old.job,:old.mgr, :old.sal, :old.comm, :old.hiredate );
END;
DELETE emp WHERE empno=7788;
DROP TABLE emp_his;
DROP TRIGGER del_emp;

CREATE OR REPLACE TRIGGER tr_dept_time
BEFORE INSERT OR DELETE OR UPDATE
ON departments
BEGIN
 IF (TO_CHAR(sysdate,'DAY') IN ('星期六', '星期日')) OR (TO_CHAR(sysdate, 'HH24:MI') NOT BETWEEN '08:30' AND '18:00') THEN
 RAISE_APPLICATION_ERROR(-20001, '不是上班時間，不能修改departments表');
 END IF;
END;

CREATE OR REPLACE TRIGGER tr_emp_sal_comm
BEFORE UPDATE OF salary, commission_pct
 OR DELETE
ON HR.employees
FOR EACH ROW
WHEN (old.department_id = 80)
BEGIN
 CASE
 WHEN UPDATING ('salary') THEN
 IF :NEW.salary  :old.salary THEN

  RAISE_APPLICATION_ERROR(-20001, '部門80的人員的工資不能降');
 END IF;
 WHEN UPDATING ('commission_pct') THEN

 IF :NEW.commission_pct  :old.commission_pct THEN
  RAISE_APPLICATION_ERROR(-20002, '部門80的人員的獎金不能降');
 END IF;
 WHEN DELETING THEN
  RAISE_APPLICATION_ERROR(-20003, '不能刪除部門80的人員記錄');
 END CASE;
END;

/*
實例：
UPDATE employees SET salary = 8000 WHERE employee_id = 177;
DELETE FROM employees WHERE employee_id in (177,170);
*/

CREATE OR REPLACE TRIGGER tr_reg_cou
AFTER update OF region_id
ON regions
FOR EACH ROW
BEGIN
 DBMS_OUTPUT.PUT_LINE('舊的region_id值是'||:old.region_id
   ||'、新的region_id值是'||:new.region_id);
 UPDATE countries SET region_id = :new.region_id
 WHERE region_id = :old.region_id;
END;

CREATE OR REPLACE PROCEDURE add_job_history
 ( p_emp_id  job_history.employee_id%type
 , p_start_date job_history.start_date%type
 , p_end_date job_history.end_date%type
 , p_job_id  job_history.job_id%type
 , p_department_id job_history.department_id%type
 )
IS
BEGIN
 INSERT INTO job_history (employee_id, start_date, end_date,
    job_id, department_id)
 VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
END add_job_history;

--創(chuàng)建觸發(fā)器調用存儲過程...
CREATE OR REPLACE TRIGGER update_job_history
 AFTER UPDATE OF job_id, department_id ON employees
 FOR EACH ROW
BEGIN
 add_job_history(:old.employee_id, :old.hire_date, sysdate,
   :old.job_id, :old.department_id);
END;