---------- trigger_minzu.txt ----------
/*
 * 范例名稱：觸發(fā)器
 * 文件名稱：trigger_minzu.txt
 */

--最簡單的觸發(fā)器----------minzu_trigger1---------------------
alter session set nls_language ='simplified chinese';


create or replace trigger han 
before insert 
on man 

begin 
    --不是在2000不許插入
    if to_char(sysdate,'yyyy') !='2000' then 
       
	   RAISE_APPLICATION_ERROR (-20501, '必須在2000年.');
	end if;
end;

insert into man values('aa','a')
            *
ERROR 位于第 1 行:
ORA-20501: 必須在2000年.
ORA-06512: 在"SCOTT.HAN", line 7
ORA-04088: 觸發(fā)器 'SCOTT.HAN' 執(zhí)行過程中出錯
--注意：
RAISE_APPLICATION_ERROR:這一語句升起一個用戶定義錯誤，顯示一條用戶定義提示
raise_application_error lets
you issue user-defined error messages 
錯誤號必須在當在-20000 .. -20999之間
error_number is a negative integer in the range -20000 .. -20999


--使用or讓delete 和insert全觸發(fā)---trigger_or----------------------
 

----------------for eache row-----------------------

create or replace trigger han before insert 
on man
for each row
begin
    if :new.minzu !='漢' then 
        raise_application_error(-20502,'不是漢族');
    end if;

end;
insert into man values('aa','a');
ERROR 位于第 1 行:
ORA-20502: 不是漢族
ORA-06512: 在"SCOTT.HAN", line 3
ORA-04088: 觸發(fā)器 'SCOTT.HAN' 執(zhí)行過程中出錯





  
---------- trigger_row.txt ----------
/*
 * 范例名稱：行級觸發(fā)器
 * 文件名稱：trigger_row.txt
 */
 --必須首先DISABLE THE TRIGGER: SCOTT.SECURE_EMP THEN INSERT.
 ALTER TRIGGER SCOTT.SECURE_EMP DISABLE ;

--CREATE TABLE :audit_emp_values
CREATE TABLE audit_emp_values
(user_name VARCHAR2(50),
timestamp  DATE, 
id NUMBER(4),
old_last_name VARCHAR2(10), 
new_last_name VARCHAR2(10),
old_title  VARCHAR2(10), 
new_title VARCHAR2(10), 
old_salary NUMBER(7,2), 
new_salary NUMBER(7,2));



--CREATE TRIGGER USE :old   and   :new兩個內置變量
CREATE OR REPLACE TRIGGER audit_emp_values
AFTER 
DELETE OR INSERT OR UPDATE ON emp
FOR EACH ROW
 BEGIN
    INSERT INTO audit_emp_values (user_name,
     timestamp, id, old_last_name, new_last_name,
      old_title, new_title, old_salary, new_salary)
    VALUES (USER, SYSDATE, :old.empno, :old.ename,
   :new.ename, :old.job, :new.job, :old.sal, :new.sal);
 END;

--test :insert into emp對emp表插入數(shù)據(jù)，測試audit_emp_values觸發(fā)器
   insert into emp (empno,job,sal) values(8,'hunter',100);
   update emp set sal=101  where empno=4;
   --更新0行不產(chǎn)生記錄.
   insert into emp (empno,job,sal) values(6,'hunter',100);

   select count(*) from emp where job='hunter';
   --驗證FOR EACH ROW觸發(fā)次數(shù)
   delete from emp where job='hunter';





--DISABLE觸發(fā)器，并測試
--ALTER TRIGGER audit_emp_values DISABLE ;

INSERT INTO emp VALUES (999,'TOM999','DEVELOPER',9,NULL,9999.99,9,10);
--IF OK ,SELECT 
SELECT * FROM audit_emp_values;

--DISABLE A TRIGGER
ALTER TRIGGER audit_emp_values ENABLE;
--DISABLE觸發(fā)器
ALTER TABLE emp   DISABLE ALL TRIGGERS;
--TEST:測試
 INSERT INTO emp VALUES (998,'TOM999','DEVELOPER',9,NULL,9999.99,9,10);
ALTER TABLE emp   ENABLE  ALL TRIGGERS;
--TEST:測試
 INSERT INTO emp VALUES (995,'TOM995','DEVELOPER',9,NULL,9999.99,9,10);

--DEL DATA INSERTED INTO FOR THE NEXT TEST
DELETE FROM emp WHERE empno IN (999,998,997);













---------- mutating_table.txt ----------
/*
 * 范例名稱：mutating table:變異表
 * 文件名稱：mutating_table.txt
 */

--create trigger check_salary
CREATE OR REPLACE TRIGGER check_salary
BEFORE INSERT OR UPDATE  ON emp
FOR EACH ROW
DECLARE
  	v_minsalary emp.sal%TYPE;
  	v_maxsalary emp.sal%TYPE;
BEGIN
  	SELECT MIN(sal), MAX(sal)
  	INTO	  v_minsalary, v_maxsalary
 	FROM	  emp;
 
 	IF :new.sal < v_minsalary OR :new.sal > v_maxsalary THEN
 	    RAISE_APPLICATION_ERROR(-20505,  'Out of range');
 	END IF;
END;

--test, fire trigger check_salary
SELECT * FROM emp WHERE ename = 'TOM999';--如果沒有相應記錄，insert相應記錄.

insert into emp (empno,ename,job,sal) values(999,'TOM999','hunter',999);

UPDATE emp
SET sal = 1500
WHERE ename = 'TOM999';
--實際1500是合法數(shù)據(jù)
 select MIN(sal),MAX(sal) from emp;


--result
/*
ERROR 位于第 2 行:
ORA-04091: table SCOTT.EMP is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.CHECK_SALARY", line 5

ORA-04088: error during execution of trigger 'SCOTT.CHECK_SALARY'
*/