有一个表表名 shishi 字段 a ,b, c ,kind
有一条记录 001,3,3,t
我做个触发器 当插入insert into shishi(a,kind) values(001,'f')时,自动能从上a=001和kind=t的记录中取出数据填充到插入的新数据中;
也就是 说 当我写 insert into shishi(a,kind) values(001,'f')时 ,数据库增加条数据 001,3,3,f;
比较急,谢谢了这是我写的 通不过编译 不晓得为什么create or replace trigger tr_ht after insert on shishi
referencing old as old_value new as new_value for each row
declare bb int;
begin
select bb=b from shishi where a=3 and kind='t';
update shishi set b=bb where a=:new_value.a and kind='f';
end;
有一条记录 001,3,3,t
我做个触发器 当插入insert into shishi(a,kind) values(001,'f')时,自动能从上a=001和kind=t的记录中取出数据填充到插入的新数据中;
也就是 说 当我写 insert into shishi(a,kind) values(001,'f')时 ,数据库增加条数据 001,3,3,f;
比较急,谢谢了这是我写的 通不过编译 不晓得为什么create or replace trigger tr_ht after insert on shishi
referencing old as old_value new as new_value for each row
declare bb int;
begin
select bb=b from shishi where a=3 and kind='t';
update shishi set b=bb where a=:new_value.a and kind='f';
end;
select bb into :new.a from shishi where a=3 and kind='t';
end;
referencing old as old_value new as new_value for each row
declare bb int;
begin
select b into bb from shishi where a='001' and kind='t';
new.b:=bb;
end;
referencing old as old_value new as new_value for each row
declare bb int;
begin
select b into bb from shishi where a='001' and kind='t';
new.b:=bb;
end;
比如:
insert into shishi(a,kind) values(001,'f');
UPDATE SHISHI SET (B,c)=(select b,c from shishi where a='001' and kind='t')
WHERE A = '001'
AND KIND = 'f';
我这样多可以
create or replace trigger tr_ht after insert on shishi
begin
update shishi set b=5,c=22 where a=:old.a and kind ='f' ;
end;
create or replace trigger tr_ht after insert on shishi
begin
update shishi set b=5,c=22 where a='001' and kind ='f' ;
end;但这样就不行了
create or replace trigger tr_ht after insert on shishi for each row
begin
update shishi set b=5,c=22 where a=:new.a and kind ='f' ;
end; 就报这个错
ORA-04091: 表 NC50.SHISHI 发生了变化, 触发器/函数不能读它
ORA-06512: 在 "NC50.TR_HT", line 2
ORA-04088: 触发器 'NC50.TR_HT' 执行过程中出错View program sources of error stack?
before insert on shishi
for each row
declare
v_b number;
v_c number;
begin
select b,c into v_b,v_c from shishi where a=3 and kind='t';
:NEW.b := v_b;
:NEW.c := v_c;
end;
BEFORE INSERT ON SHISHI
FOR EACH ROW
DECLARE
V_B VARCHAR2(10);
V_C VARCHAR2(10);
BEGIN
SELECT B, C
INTO V_B, V_C
FROM SHISHI
WHERE A = :new.a
AND KIND = 't';
:NEW.B := V_B;
:NEW.C := V_C;
END;
直接update就行
UPDATE SHISHI SET B=3;
UPDATE SHISHI SET C=3;
不加条件不就这两列都更新了么 呵呵
定义个变量
用CURSOR取行记录中的两个字段
SELECT CURSOR INTO 变量
INSERT INTO()VALUES(变量.B,变量.c)
即时的要用触发器create or replace trigger tr_ht
before insert on shishi
for each row
declare
begin
:NEW.b := 3;
:NEW.c := 3;
end;如果条件能限制的话
create or replace trigger tr_ht
before insert on shishi
declare
v_b number;
v_c number;
begin
select b,c into v_b,v_c from shishi where a=3 and kind='t';
:NEW.b := v_b;
:NEW.c := v_c;
end;