oracle数据库设定用户修改权限

–1.禁止员工在休息日改变雇员信息

create or replace trigger tri_a
before update or insert or delete on emp
for each row
begin
if to_char(sysdate,’day’) in (‘Saturday’,’Sunday’) or to_char(sysdate,’hh24:mi’) not
between ’08:00′ and ’17:00′ then
raise_application_error(-20001,’非工作时间不能更改emp表’);
end if;
end;

–测试

delete from emp;

–2.限制员工的工资不能超过当前的最高工资

create or replace trigger tri_a
before insert or update of sal on emp
for each row
declare
maxSal number;
pragma autonomous_transaction;–设计自制事务
begin

select max(sal) into maxSal from emp;
if :new.sal>maxSal then
raise_application_error(-20001,’工资不能大于当前最大的工资’);
end if;
commit;–涉及自制事务
end;

–查询当前的最大工资

select max(sal) from emp;

–测试

 

insert into emp (empno,ename,sal) values(1,’dssd’,7000);
update emp set sal = 7000 where empno = 7369 ;–牵扯到自制事务的问题

–3.设置员工的工资不能低于原工资，但也不能高出原工资的20%

create or replace trigger tri_a
before update of sal on emp
for each row

begin
if :new.sal<:old.sal or :new.sal>:old.sal*1.2 then
raise_application_error(-20001,’员工的工资不能低于原工资，但也不能高于原工资的20%’);
end if;
end;

–测试

update emp set sal = 700 where empno= 7369;