课外天地 李树青学习天地数据库系统原理课件 → 利用Oracle触发器实现的生日年龄自动管理


  共有21699人关注过本帖树形打印复制链接

主题:利用Oracle触发器实现的生日年龄自动管理

帅哥哟,离线,有人找我吗?
admin
  1楼 博客 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信 管理员
等级:管理员 帖子:1945 积分:26736 威望:0 精华:34 注册:2003/12/30 16:34:32
利用Oracle触发器实现的生日年龄自动管理  发帖心情 Post By:2008/11/29 13:36:40 [只看该作者]

假设当前用户为lsq:

create table stu1
(
sid varchar2(6) NOT NULL,
birthday timestamp
);
create table stu2
(
sid varchar2(6) NOT NULL,
age number(3)
);

insert into stu1 values('1',TO_DATE('21-4月 -1985','dd-Mon-yyyy'));
--error
insert into stu2 values('1',15);

select * from stu1;
select * from stu2;
delete from stu1;
delete from stu2;

CREATE OR REPLACE TRIGGER InsertStu1
  BEFORE INSERT ON stu1
  FOR EACH ROW
DECLARE
  v_sid varchar2(6);
  v_age number(3);
BEGIN
  v_sid:=:new.sid;
  v_age:=extract(year from sysdate)-extract(year from :new.birthday);
  insert into stu2 values(v_sid,v_age);
END InsertStu1;
/

insert into stu1 values('1',TO_DATE('21-4月 -1985','dd-Mon-yyyy'));
select * from stu1;
select * from stu2;
--error
update stu1 set birthday=TO_DATE('21-4月 -1984','dd-Mon-yyyy') where sid='1';
select * from stu1;
select * from stu2;
delete from stu1;
delete from stu2;

CREATE OR REPLACE TRIGGER UpdateStu1
  BEFORE UPDATE ON stu1
  FOR EACH ROW
DECLARE
  v_sid varchar2(6);
  v_age number(3);
BEGIN
  v_sid:=:old.sid;
  v_age:=extract(year from sysdate)-extract(year from :new.birthday);
  update stu2 set age=v_age where sid=v_sid;
END UpdateStu1;
/

insert into stu1 values('1',TO_DATE('21-4月 -1985','dd-Mon-yyyy'));
select * from stu1;
select * from stu2;
update stu1 set birthday=TO_DATE('21-4月 -1984','dd-Mon-yyyy') where sid='1';
select * from stu1;
select * from stu2;
--error
delete from stu1 where sid='1';
select * from stu1;
select * from stu2;
delete from stu1;
delete from stu2;

CREATE OR REPLACE TRIGGER DeleteStu1
  BEFORE delete ON stu1
  FOR EACH ROW
DECLARE
  v_sid varchar2(6);
BEGIN
  v_sid:=:old.sid;
  delete stu2 where sid=v_sid;
END DeleteStu1;
/

insert into stu1 values('1',TO_DATE('21-4月 -1985','dd-Mon-yyyy'));
select * from stu1;
select * from stu2;
delete from stu1 where sid='1';
delete from stu1;
delete from stu2;

更好的处理方案,使得用户不可能对不应该直接操纵的表进行操作
Connect system/sys@orcl;
create user newuser identified by newuser;
grant select on lsq.stu1 to newuser;
grant select on lsq.stu2 to newuser;
grant connect to newuser;
grant update,insert,delete on lsq.stu1 to newuser;

Connect newuser/newuser@orcl;
select * from lsq.stu1;
insert into lsq.stu1 values('1',TO_DATE('21-4月 -1985','dd-Mon-yyyy'));
select * from lsq.stu1;
select * from lsq.stu2;
update lsq.stu1 set birthday=TO_DATE('21-4月 -1984','dd-Mon-yyyy') where sid='1';
select * from lsq.stu1;
select * from lsq.stu2;
delete from lsq.stu1 where sid='1';
select * from lsq.stu1;
select * from lsq.stu2;

--error
insert into lsq.stu2 values('1',24);
update lsq.stu2 set age=22 where sid='1';

[此贴子已经被作者于2010-12-11 20:04:56编辑过]

 回到顶部