oracle 笔记6 触发器

--触发器  何云祥制作
create or replace trigger secure_test
  before insert on test
  begin
      if(to_char(sysdate,'dy') in ('星期六','星期日')) or (to_char(sysdate,'hh24:mi') not between '08:00' and '18:00')
      then
          raise_application_error(-20500,'你只能在工作时间对test表进行操作.');
      end if;
  end;
--对DML都进行触发
create or replace trigger secure_test
  before insert or update or delete on test
  begin
      if(to_char(sysdate,'dy') in ('星期六','星期日')) or (to_char(sysdate,'hh24:mi') not between '08:00' and '18:00')
      then
          if deleting then
              raise_application_error(-20502,'你只能在工作时间对test表进行删除操作.');
          elsif inserting then
              raise_application_error(-20500,'你只能在工作时间对test表进行插入操作.');
          elsif updating('name') then
              raise_application_error(-20503,'你只能在工作时间对test表进行name更新操作.');
          else
              raise_application_error(-20504,'你只能在工作时间对test表进行更新操作.');
          end if;
      end if;
  end;
--触发器中使用变量
create or replace trigger v_trig instead of
update or delete or insert on v_emp
 declare
 l_res varchar(20):='';
 BEGIN
  IF   DELETING THEN
     l_res:='You may delete .';
   ELSIF  INSERTING THEN
      l_res:='You may insert.';
   ELSIF   UPDATING ('SALARY') THEN
     l_res:='You may update.';
   ELSE
      l_res:='You may update.';
   END IF;
   insert into e_msg values(111,l_res,sysdate);
END;
--行级触发器
CREATE OR REPLACE TRIGGER restrict_salary
  before  INSERT OR UPDATE OF slary ON employee

    FOR EACH ROW
    BEGIN
    IF :new.slary>1400 THEN
        RAISE_APPLICATION_ERROR (-20202,'员工不能赚到这么多薪水');

      end if;

    END;
--使用when的行级触发器
CREATE OR REPLACE TRIGGER restrict_salary
  before  INSERT OR UPDATE OF slary ON employee

    FOR EACH ROW
    when (new.slary>1400 )
    BEGIN
        RAISE_APPLICATION_ERROR (-20202,'员工不能赚到这么多薪水');
    END;
--启用或者禁用某个触发器:
alter trigger trigger_name disable|enable
--启用或者禁用某个对象上的所有触发器
alter table table_name disable|enable all triggers
--重新编译触发器
alter trigger trigger_name compile
--某个系统触发器
CREATE OR REPLACE TRIGGER logon_trig
AFTER drop  ON  SCHEMA
BEGIN
 INSERT INTO e_msg values (seq_2.nextval, user||' drop', sysdate);
END;