---------- trigger_test.txt ----------
/*
 * 范例名稱：觸發(fā)器
 * 文件名稱：trigger_test.txt
 */
 --首先使用alter session set nls_language =american;更改語言為英文。
 --因為下面TO_CHAR (sysdate,'DY') IN ('SAT','SUN'))使用的為SAT，sun
alter session set nls_language =american;

--alter session set nls_language ='simplified chinese';

--DEFINE A TRIGGER
CREATE OR REPLACE TRIGGER secure_emp
before INSERT ON emp
--BEFORE INSERT ON emp
  BEGIN
	  --如果不在上班時間
	  IF (TO_CHAR (sysdate,'DY') IN ('SAT','SUN'))  OR (TO_CHAR(sysdate,'HH24')) NOT BETWEEN  '08' AND '18' 
	  THEN
	   RAISE_APPLICATION_ERROR (-20500, 'You may only insert into EMP during normal hours.');
	  END IF;
  END;

--INSERT INTO 如果不在上班時間，TRIGGER報錯！
INSERT INTO emp (empno, ename, deptno)
VALUES          (7778, 'BAUWENS', 20);
TRIGGER報錯,紀(jì)錄不能插入。
select * from emp where empno=7778;


/*
RAISE_APPLICATION_ERROR:
raise_application_error lets
you issue user-defined error messages 

error_number is a negative integer in the range -20000 .. -20999

ERROR 位于第 1 行:
ORA-20500: You may only insert into EMP during normal hours.
上一行就是RAISE_APPLICATION_ERROR產(chǎn)生的結(jié)果
ORA-06512: at "SCOTT.SECURE_EMP", line 4
ORA-04088: error during execution of trigger 'SCOTT.SECURE_EMP'
*/


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

--CREATE TABLE :audit_emp_values:建立一個對emp的監(jiān)視表
--紀(jì)錄dml的執(zhí)行用戶，dml前表的值，dml之后的值
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兩個內(nèi)置變量
CREATE OR REPLACE TRIGGER audit_emp_values
AFTER DELETE OR INSERT OR UPDATE ON emp
FOR EACH ROW
 BEGIN
    
    --插入audit_emp_values
    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;
   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 THE TRIGGER: SCOTT.SECURE_EMP THEN INSERT.

--DISABLE觸發(fā)器，并測試
ALTER TRIGGER SCOTT.SECURE_EMP 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 SCOTT.SECURE_EMP DISABLE;
--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 (998,'TOM999','DEVELOPER',9,NULL,9999.99,9,10);

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









---------- constraint_trigger.txt ----------
/*
 * 范例名稱：Constraining Table
 * 文件名稱：constraint_trigger.txt
 */

DROP TABLE dept_new;

建立一個dept_new表進行測試
CREATE TABLE dept_new
(deptno NUMBER(3) PRIMARY KEY,
dname  varchar2(10));

--將emp表所有deptno 插入dept_new
INSERT INTO dept_new VALUES (30,'DEPT3');
INSERT INTO dept_new VALUES (20,'DEPT2'); 
INSERT INTO dept_new VALUES (10,'DEPT1'); 

--add constranit
ALTER TABLE emp ADD CONSTRAINT fk_dept FOREIGN KEY(deptno) REFERENCES dept_new(deptno);
alter table emp drop constraint fk_dept;

--create trigger:修改constranit表（dept_new）的primary key.
CREATE OR REPLACE TRIGGER cascade_updates
  AFTER UPDATE  on emp
  FOR EACH ROW
   BEGIN
   	
 	UPDATE dept_new
 	SET    dept_new.deptno = :new.deptno
	WHERE  dept_new.deptno = :old.deptno;
   END;

--emp update test
UPDATE emp SET    deptno = 20
WHERE  deptno = 40;

select * from emp where deptno=40;
--如果update沒有對應(yīng)紀(jì)錄，則不會有錯。

UPDATE emp SET    deptno = 30
WHERE  deptno = 20;
/*
ERROR at line 1:
ORA-04091: table SCOTT.EMP is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.CHECK_SALARY", line 9
ORA-04088: error during execution of trigger 'SCOTT.CHECK_SALARY'

*/



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

--create trigger check_salary
CREATE OR REPLACE TRIGGER check_salary
BEFORE INSERT OR UPDATE OF sal 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 = 'SIMTH';--如果沒有相應(yīng)記錄，insert相應(yīng)記錄.

UPDATE emp
SET sal = 1500
WHERE ename = 'SMITH';

--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'
*/