--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
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