oracle高级用法之触发器
·
/* 实施复杂的安全性检查：禁止在非工作时间插入员工 */
create or replace trigger securityemp
before insert
on emp
begin
if to_char(sysdate,'day') in ('星期六','星期天') or
to_number(to_char(sysdate,'hh24')) not between 9 and 18 then
raise_application_error(-20001,'禁止在非工作时间插入新员工');
end if;
end;
-- 删除触发器
drop trigger securityemp;
insert into emp (empno,ename,sal,deptno) values(1001,'libing',8000,10);
/* 数据的确认：涨后的薪水不能少于涨前的薪水 */
create or replace trigger checksalary
before update
on emp
for each row
begin
if :new.sal < :old.sal then
raise_application_error('20002','涨后的薪水不能少于涨前的薪水:||:new.sal||:old.sal');
end if;
end;
update emp t set t.sal = t.sal -1 where t.ename = 'libing';
/* 数据的审计：涨后的工资超过6000，审计该员工 */
-- 创建审计表
create table audit_info(
information varchar2(200)
);
create or replace trigger do_audit_emp_salary
after update
on emp
for each row
begin
if :new.sal > 6000 then
insert into audit_info values(:new.empno||' '||:new.ename||' '||:new.sal);
end if;
end;
update emp t set t.sal = t.sal + 1 where t.ename = 'libing';
commit;
select * from audit_info;
/* 实现数据的同步和备份:给员工涨完工资后，自动备份工资到备份表 */
-- 创建备份表
create table emp_back as select * from emp;
create or replace trigger sync_salary
after update
on emp
for each row
begin
update emp_back set sal=:new.sal where empno =:new.empno;
end;
update emp set sal=sal+10 where empno=1001;
create or replace trigger securityemp
before insert
on emp
begin
if to_char(sysdate,'day') in ('星期六','星期天') or
to_number(to_char(sysdate,'hh24')) not between 9 and 18 then
raise_application_error(-20001,'禁止在非工作时间插入新员工');
end if;
end;
-- 删除触发器
drop trigger securityemp;
insert into emp (empno,ename,sal,deptno) values(1001,'libing',8000,10);
/* 数据的确认：涨后的薪水不能少于涨前的薪水 */
create or replace trigger checksalary
before update
on emp
for each row
begin
if :new.sal < :old.sal then
raise_application_error('20002','涨后的薪水不能少于涨前的薪水:||:new.sal||:old.sal');
end if;
end;
update emp t set t.sal = t.sal -1 where t.ename = 'libing';
/* 数据的审计：涨后的工资超过6000，审计该员工 */
-- 创建审计表
create table audit_info(
information varchar2(200)
);
create or replace trigger do_audit_emp_salary
after update
on emp
for each row
begin
if :new.sal > 6000 then
insert into audit_info values(:new.empno||' '||:new.ename||' '||:new.sal);
end if;
end;
update emp t set t.sal = t.sal + 1 where t.ename = 'libing';
commit;
select * from audit_info;
/* 实现数据的同步和备份:给员工涨完工资后，自动备份工资到备份表 */
-- 创建备份表
create table emp_back as select * from emp;
create or replace trigger sync_salary
after update
on emp
for each row
begin
update emp_back set sal=:new.sal where empno =:new.empno;
end;
update emp set sal=sal+10 where empno=1001;
北京人形旗下天工造物具身智能开源社区，聚焦具身天工与慧思开物两大平台
更多推荐
0
0- 0
已为社区贡献3条内容
所有评论(0)