script_82_withconstrainttable.txt

orale培训教材包括了所有的sql说明和实例

---------- trigger_test.txt ----------
/*
 * 范例名称：触发器
 * 文件名称：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报错,纪录不能插入。
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产生的结果
ORA-06512: at "SCOTT.SECURE_EMP", line 4
ORA-04088: error during execution of trigger 'SCOTT.SECURE_EMP'
*/


  
---------- trigger_row.txt ----------
/*
 * 范例名称：行级触发器
 * 文件名称：trigger_row.txt
 */

--CREATE TABLE :audit_emp_values:建立一个对emp的监视表
--纪录dml的执行用户，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两个内置变量
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表插入数据，测试audit_emp_values触发器
   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触发次数
   delete from emp where job='hunter';



--必须首先DISABLE THE TRIGGER: SCOTT.SECURE_EMP THEN INSERT.

--DISABLE触发器，并测试
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触发器
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没有对应纪录，则不会有错。

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';--如果没有相应记录，insert相应记录.

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