更新一行以更新另一张表时如何创建触发器 [英] How to create trigger when one rows is updated to update another table

问题描述

我有两个表，其中包含带有产品ID的引用。表如下所示。

I have two tables, that has references with ID of products. Tables are like below.

表1：ID，Rgrupa
表2：ID，Rgrupa

Table 1 : ID, Rgrupa Table 2: ID, Rgrupa

我想创建触发器，当表1中的Rgrupa被更新时，该触发器将针对相同的产品ID从表2中更新Rgrupa。

I would like to create trigger, that will update Rgrupa from table 2, when Rgrupa is updated in table 1, for the same ID of product.

我已经尝试过

create or replace trigger test 
after update of rgrupa on table1 
begin 
update table2 t2 
set t2.rgrupa = :new.rgrupa 
where t2.id = :new.id;
end; 

错误消息是：
错误：ORA-04082：不允许新的或旧的引用在表级触发器中

And ERROR MESSAGE is: Error: ORA-04082: NEW or OLD references not allowed in table level triggers

推荐答案

我使用以下结构创建触发器

I used below structure to create the trigger

create table table1 (id int, rgrupa varchar2(100));
create table table2 (id int, rgrupa varchar2(100));

insert into table1 (id,rgrupa) values (1,'A');
insert into table1 (id,rgrupa) values (2,'B');
insert into table1 (id,rgrupa) values (3,'C');

insert into table2 (id,rgrupa) values (1,'A');
insert into table2 (id,rgrupa) values (2,'B');

commit;

select * from table1;
select * from table2;

create or replace trigger sandeeptest after update of rgrupa on table1 for each row
begin
update table2 set rgrupa=:new.rgrupa where id=:new.id;
end;

update table1 set rgrupa='Aa' where rgrupa='A';

update table1 set rgrupa='Cc' where rgrupa='C';

这篇关于更新一行以更新另一张表时如何创建触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！