oracle 触发器 条件判断6,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_trigAFTER drop  ON  SCHEMABEGIN INSERT INTO e_msg values (seq_2.nextval, user||' drop', sysdate);END;