-- 触发器

-- before 语句触发器

--create or replace trigger tr_src_emp 

--   before insert or update or delete on emp

--begin

--   if to_char(sysdate,'DY','nls_date_language=AMERICAN') in ('SAT','SUN') then

--      raise_application_error(-20001,'can't modify user information in weekend');

--   end if;

--end;


-- 使用条件谓语

create or replace trigger tr_src_emp

   before insert or update or delete on emp

begin

   if to_char(sysdate,'DY') in ('星期六','星期天') then

     case

       when inserting then

         raise_application_error(-20001,'fail to insert');

       when updating then

         raise_application_error(-20001,'fail to update');

       when deleting then

         raise_application_error(-20001,'fail  to delete');

     end case;  

   end if;

end;

-- after 语句触发器

Create table audit_table(Name varchar2(20),ins int,upd int,del int,starttime date,endtime date);

Create or replace trigger tr_audit_emp After insert or update or delete on emp 

Declare

  v_temp int; 

Begin

  Select count(*) into v_temp from audit_table

    Where name='EMP'; 

    If v_temp=0 then

       Insert into audit_table values('EMP',0,0,0,sysdate,null); 

    End if;

    Case

        When inserting then

             Update audit_table set ins=ins+1,endtime=sysdate where name='EMP'; 

        When updating then

             Update audit_table set upd=upd+1,endtime=sysdate where name='EMP'; 

        When deleting then

             Update audit_table set del= del +1,endtime=sysdate where name='EMP'; 

    End case;

End;

-- before 行触发器

create or replace trigger tr_emp_sal

  before update of sal on emp

  for each row

begin

  if :new.sal < :old.sal then

    raise_application_error(-200010,'sal should not be less');

  end if;

end;

-- after 行触发器

Create table audit_emp_change( 

  Name varchar2(10),

  Oldsal number(6,2),

  Newsal number(6,2), 

  Time date

);

Create or replace trigger tr_sal_sal 

  after update of sal on emp

  for each row 

  declare v_temp int;

begin

  select count(*) into v_temp from audit_emp_change where name=:old.ename;

  if v_temp=0 then

     insert into audit_emp_change values(:old.ename,:old.sal,:new.sal,sysdate);

  else

     update audit_emp_change set oldsal=:old.sal,newsal=:new.sal,time=sysdate where name=:old.ename;

  end if;

end;

-- 限制行触发器

Create or replace trigger tr_sal_sal 

  after update of sal on emp

  for each row

  when (old.job='SALESMAN')

    declare v_temp int; 

    begin

      select count(*) into v_temp from audit_emp_change where name=:old.ename;

      if v_temp=0 then

        insert into audit_emp_change values(:old.ename,:old.sal,:new.sal,sysdate);

      else

        update audit_emp_change set oldsal=:old.sal,newsal=:new.sal,time=sysdate where name=:old.ename;

      end if;

    end;