script_78.txt

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

---------- trigger_minzu.txt ----------
/*
 * 范例名称：触发器
 * 文件名称：trigger_minzu.txt
 */

--最简单的触发器----------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: 触发器 'SCOTT.HAN' 执行过程中出错
--注意：
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全触发---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: 触发器 'SCOTT.HAN' 执行过程中出错





  
---------- trigger_row.txt ----------
/*
 * 范例名称：行级触发器
 * 文件名称：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表插入数据，测试audit_emp_values触发器
   insert into emp (empno,job,sal) values(8,'hunter',100);
   update emp set sal=101  where empno=4;
   --更新0行不产生记录.
   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触发器，并测试
--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触发器
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是合法数据
 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'
*/