-- 作者:admin
-- 发布时间:2008/11/29 13:36:40
-- 利用Oracle触发器实现的生日年龄自动管理
假设当前用户为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编辑过]
|