有两个表A,B
A
A1
IDB
B1
ID请教建一个触发器,当对B表进行修改或插入的时候,在完成操作后,再执行 update b set b.b1=a.a1 where b.id=a.id;
create or replace trigger test_A_B
after insert or update
on B
for each row
begin -- 执行 update b set b.b1=a.a1 where b.id=a.id;end;
请教各位大牛。
谢谢。
A
A1
IDB
B1
ID请教建一个触发器,当对B表进行修改或插入的时候,在完成操作后,再执行 update b set b.b1=a.a1 where b.id=a.id;
create or replace trigger test_A_B
after insert or update
on B
for each row
begin -- 执行 update b set b.b1=a.a1 where b.id=a.id;end;
请教各位大牛。
谢谢。
create or replace trigger test_A_B
after insert or update
on A --如果是修改A表 引发的话 这里用A
for each row
begin
update b set b.b1=:new.a1 where b.id=:new.id;
end;
我是这样写的
create or replace trigger test_A_B
after insert or update
on B
for each row
begin
update b set :new.b1=a.a1 where :new.id=a.id;
end;报错:PL/SQL:ORA-01747:user.table.column,table.column或列说明无效
PL/SQL:SQL Statement ignored
--你的after触发器是不能修改:new值的,要改成before触发器:
create or replace trigger test_A_B
before insert or update on B
for each row
begin
select a.a1 into :new.b1 where a.id=:new.id and rownum=1;
exception when others then
null;
end;
after insert or update
on B
reference old as old new as new
for each row
begin
If inserting then
update b set b.b1=a.a1 where b.id=:new.id and b.id=a.id;
If updating then
update b set b.b1=a.a1 where b.id=a.id
end;
create or replace trigger tri_a_b
after insert or update
on B
reference old as old new as new
for each row
begin
If inserting then
update b set b.b1=a.a1 where b.id=:new.id and b.id=(select a.id from A where a.id=:new.id);
If updating then
update b set b.b1=a.a1 where b.id=(select a.id from A where a.id=:new.id);
end;
create table tableb (b1 number,id number);
insert into tablea
select mod(rownum,3),rownum from dual connect by rownum<=20;SQL> CREATE OR REPLACE TRIGGER tri_tableb
2 BEFORE INSERT OR UPDATE ON tableb
3 FOR EACH ROW
4 DECLARE
5 v_num NUMBER;
6 BEGIN
7 SELECT a1 INTO v_num FROM tablea t WHERE t.id = :NEW.id;
8 :NEW.b1 := v_num;
9 END;
10 /
Trigger created
SQL> insert into tableb values(5,1);
1 row inserted
SQL> select * from tableb;
B1 ID
---------- ----------
1 1
SQL>
另外就是插入表b时,必须保证插入b的id在a中已经存在.
不管对 B 表进行更新还是插入操作,不管把B.b1改成什么值,都要再将b.b1=a.a1,都始终跟a.a1保持相等。
那应该用什么触发器?
我是这样写的 只写的update 没写 insertcreate or replace trigger test_a_b
after update
on B
for each row
begin
update B set B.b1=A.a1 where B.id=A.id and A.id=old.id
end;
报错 ora-00904: A.ID:标识符无效请教各位高手。或者 还有什么其它的方法能实现这种效果。