--触发器 何云祥制作
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;