不知道你的自动带出姓名是什么意思,以下是通过自治事务解决同表问题,弊端是不能对正在操作的同一条记录进行更改,否则会造成deadlock。 以下更改T1时,将更改的记录存储在T2中, create table t1(id number,nm varchar2(100)); create table t2(id number,nm varchar2(100));insert into t2 values(1,'n1');CREATE OR REPLACE TRIGGER test before update ON t1 FOR EACH ROW declare PRAGMA AUTONOMOUS_TRANSACTION; BEGIN insert into t2 select * from t1 where id = :old.id; commit; END; update t1 set t1.id=10 where t1.id=1;commit;
不知道你的自动带出姓名是什么意思,以下是通过自治事务解决同表问题,弊端是不能对正在操作的同一条记录进行更改,否则会造成deadlock。 以下更改T1时,将更改的记录存储在T2中, create table t1(id number,nm varchar2(100)); create table t2(id number,nm varchar2(100));insert into t2 values(1,'n1');CREATE OR REPLACE TRIGGER test before update ON t1 FOR EACH ROW declare PRAGMA AUTONOMOUS_TRANSACTION; BEGIN insert into t2 select * from t1 where id = :old.id; commit; END; update t1 set t1.id=10 where t1.id=1;commit; 自动带出的意思是 如果c_teacherid改变,c_teacher则会对应改变。 相当于update emp set c_teacher= (select c_name from emp where c_oid=变化后的c_teacherid)
只有建个视图或者是表作用一个中间对象。 如下是建一个中间视图,当你对视图进行update时就能达到你要的效果: create table t1(id number,nm varchar2(10)); --建测试表 --插入测试数据 insert into t1 values(1,'ct1'); insert into t1 values(2,'ct2'); commit; --创建视图 create view v_t1 as select * from t1; --在视图上创建trigger(只能创建instead of 类型的) create or replace trigger test instead of UPDATE ON v_t1 FOR EACH ROW DECLARE BEGIN UPDATE t1 SET nm = (select nvl(nm,:old.nm) from t1 where t1.id=:new.id) WHERE id = :old.id; END t_v_test; / update v_t1 set id=2 where id=1; --更新视图的id,nm也会更新
只有建个视图或者是表作用一个中间对象。 如下是建一个中间视图,当你对视图进行update时就能达到你要的效果: create table t1(id number,nm varchar2(10)); --建测试表 --插入测试数据 insert into t1 values(1,'ct1'); insert into t1 values(2,'ct2'); commit; --创建视图 create view v_t1 as select * from t1; --在视图上创建trigger(只能创建instead of 类型的) create or replace trigger test instead of UPDATE ON v_t1 FOR EACH ROW DECLARE BEGIN UPDATE t1 SET nm = (select nvl(nm,:old.nm) from t1 where t1.id=:new.id) WHERE id = :old.id; END t_v_test; / update v_t1 set id=2 where id=1; --更新视图的id,nm也会更新 好的 非常感谢
以下更改T1时,将更改的记录存储在T2中,
create table t1(id number,nm varchar2(100));
create table t2(id number,nm varchar2(100));insert into t2 values(1,'n1');CREATE OR REPLACE TRIGGER test
before update ON t1
FOR EACH ROW
declare
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
insert into t2
select * from t1 where id = :old.id;
commit;
END;
update t1 set t1.id=10 where t1.id=1;commit;
需要导师姓名的时候就用视图。
以下更改T1时,将更改的记录存储在T2中,
create table t1(id number,nm varchar2(100));
create table t2(id number,nm varchar2(100));insert into t2 values(1,'n1');CREATE OR REPLACE TRIGGER test
before update ON t1
FOR EACH ROW
declare
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
insert into t2
select * from t1 where id = :old.id;
commit;
END;
update t1 set t1.id=10 where t1.id=1;commit;
自动带出的意思是 如果c_teacherid改变,c_teacher则会对应改变。
相当于update emp set c_teacher=
(select c_name from emp where c_oid=变化后的c_teacherid)
如下是建一个中间视图,当你对视图进行update时就能达到你要的效果:
create table t1(id number,nm varchar2(10)); --建测试表
--插入测试数据
insert into t1 values(1,'ct1');
insert into t1 values(2,'ct2');
commit;
--创建视图
create view v_t1 as select * from t1;
--在视图上创建trigger(只能创建instead of 类型的)
create or replace trigger test
instead of UPDATE ON v_t1
FOR EACH ROW
DECLARE
BEGIN
UPDATE t1
SET nm = (select nvl(nm,:old.nm) from t1 where t1.id=:new.id)
WHERE id = :old.id;
END t_v_test;
/
update v_t1 set id=2 where id=1; --更新视图的id,nm也会更新
如下是建一个中间视图,当你对视图进行update时就能达到你要的效果:
create table t1(id number,nm varchar2(10)); --建测试表
--插入测试数据
insert into t1 values(1,'ct1');
insert into t1 values(2,'ct2');
commit;
--创建视图
create view v_t1 as select * from t1;
--在视图上创建trigger(只能创建instead of 类型的)
create or replace trigger test
instead of UPDATE ON v_t1
FOR EACH ROW
DECLARE
BEGIN
UPDATE t1
SET nm = (select nvl(nm,:old.nm) from t1 where t1.id=:new.id)
WHERE id = :old.id;
END t_v_test;
/
update v_t1 set id=2 where id=1; --更新视图的id,nm也会更新
好的 非常感谢