表 A ,B 都有prod_name, prod_id 这两个字段,其中 表A是基础信息表,表B中的prod_name, prod_id都必须跟A同步,
为了在表B中保持prod_name, prod_id 这两个字段的一致性
我建立了这样的触发器:create or replace trigger A_B_ID
after
insert or update of prod_id on B
for each row
begin
:new.prod_id=(select prod_id from A where A.prod_name=:new.prod_name);
end;执行报PLS-00103错误,请问问题在哪里?谢谢
为了在表B中保持prod_name, prod_id 这两个字段的一致性
我建立了这样的触发器:create or replace trigger A_B_ID
after
insert or update of prod_id on B
for each row
begin
:new.prod_id=(select prod_id from A where A.prod_name=:new.prod_name);
end;执行报PLS-00103错误,请问问题在哪里?谢谢
:new.prod_id=(select PL/SQL赋值符号为:=2
你这触发器逻辑有问题,都已经after里,无法改变:new.prod_id值。
after
insert or update of prod_id on B
for each row
begin
:new.prod_id=(select prod_id from A where A.prod_name=:new.prod_name);
end;
1.首先这条语句:new.greadeid := select greadeid from stu where id=:new.id;
是不能这样写的,因为在你插入语入或更新时,是已经有值的:new.prod_id不能作为变量接受。2.变量不能这样等于 var_prod_id=(select prod_id from A where A.prod_name=:new.prod_name);应该为select prod_id into var_proid_id from A where A.prod_name=:new.prod_name
before
insert or update of prod_id on B
for each row
begin
:new.prod_id:=(select prod_id from A where A.prod_name=:new.prod_name);
end;这个红色部分好像有问题,就是不知道怎么处理这个部分
同样,select 如果不into进个变量也是不行的。我举个例子,看看是否多少能帮上点忙。
create table test_trigger_a (prod_id number, prod_name varchar2(20));
create table test_trigger_b (prod_id number, prod_name varchar2(20));alter table test_trigger_a add primary key(prod_id);
alter table test_trigger_b add primary key(prod_id);select * from test_trigger_a;
--no record.
select * from test_trigger_b;
--no record.--建立trigger
create or replace trigger trg_a_b_id
after
update or insert on test_trigger_a
for each row
beginif :new.prod_id = :old.prod_id then
update test_trigger_b set prod_name = :new.prod_name
where prod_id = :old.prod_id;
else
insert into test_trigger_b values(:new.prod_id,:new.prod_name);
end if;exception
when others then
dbms_output.put_line('Error occurs:' || sqlcode || substr(sqlerrm,1,100));
end;insert into test_trigger_a values(1,'carrot');
insert into test_trigger_a values(2,'brocolli');select * from test_trigger_a;
PROD_ID PROD_NAME
1 1 carrot
2 2 brocolliselect * from test_trigger_b;
PROD_ID PROD_NAME
1 1 carrot
2 2 brocolliupdate test_trigger_a set prod_name = 'cabbage' where prod_id = 2;select * from test_trigger_a;
1 1 carrot
2 2 cabbageselect * from test_trigger_b;
1 1 carrot
2 2 cabbage
比如在oracle中你用""这种格式给字符串赋值。
before
insert or update of prod_id on B
for each row
begin
select prod_id into :new.prod_id from A where A.prod_name=:new.prod_name;
end;