---------- 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