oracle笔记--触发器

--1.  禁止员工在休息日改变雇员信息
create or replace trigger tri_emp
before insert or update or delete on emp
begin
  if to_char(sysdate ,'DAY','nls_date_language=AMERICAN')
    in ('SAT','SUN') then
    case when inserting then
      raise_application_error(-20001,'不能在休息日增加雇员信息！');
       when updating then
             raise_application_error(-20001,'不能在休息日修改雇员信息！');
        when deleting then
            raise_application_error(-20001,'不能在休息日删除雇员信息！');
              end case;
              end if;
              end;
              
    ---2.    限制员工的工资不能超过当前的最高工资
    create or replace trigger tri_emp_salary before update of sal on emp
    for each row
      declare
            max_salary  number;
        begin
      select max(sal) into max_salary from emp;
      if :new.sal > max_salary then
           raise_application_error(-20010,'员工工资超出工资上限！');  
           end if;
           end;
      
          update emp set sal = 7777  where empno = 7369
          select sal from emp where empno = 7369
          
          --3.    设置员工的工资不能低于原工资，但也不能高出原工资的20%
 
          create or replace trigger tri_update_salary
          before update of sal on emp for each row
             when ( new.sal < old.sal or new.sal >old.sal*0.2)
          begin
         
                   raise_application_error(-20010,'员工工资不能降薪，也不能超出工资20%！');  
               
                   end;
                   
                   update emp set sal = 666 where empno=7369