trigger_test.txt

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

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

--DEFINE A TRIGGER
create or replace trigger check_emp 
before insert on sm_emp
begin
  
  if to_char(sysdate,'HH24') <'06' OR to_char(sysdate,'HH24') >='22' THEN
    RAISE_APPLICATION_ERROR(-20555,'必须在上班时间输入');
  END IF;
END;

--INSERT INTO 如果不在上班时间，TRIGGER报错！
INSERT INTO SM_EMP VALUES('9',9,9,9);
TRIGGER报错,纪录不能插入。
select * from sm_emp where empno=7778;


------------------带局部变量----------------------
create or replace trigger check_emp 
before insert on sm_emp
declare
  v_errmsg varchar2(100);
begin
  v_errmsg:='必须在上班时间输入1111';
  if to_char(sysdate,'HH24') <'06' OR to_char(sysdate,'HH24') >='22' THEN
    RAISE_APPLICATION_ERROR(-20555,v_errmsg);
  END IF;
END;

------------------带局部变量end----------------------
/*
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'
*/


--练习2：为sm_emp表建立一个基于insert的trigger,
--判断插入salary不可>10000,telno不可以为空。

create or replace trigger check_sal 
before insert on sm_emp 
for each row
begin 
  if (:new.salary > 10000 or :new.salary is null )then
    raise_application_error(-20222,'雇员工资不可以>10000');
  end if;

  if (:new.telno is null) then 
  raise_application_error(-20223,'必须有电话');
  end if;

end;

insert into sm_emp values('1000000001','赵云',10001,null);

--salary合法
insert into sm_emp values('1000000001','赵云',10000,null);
--
insert into sm_emp values('1000000001','赵云',10000,'62652098');
--已创建 1 行。 ok