How to create trigger when one rows is updated to

问题:

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

Table 1 : ID, Rgrupa Table 2: ID, 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.

I have tried this

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; 

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

回答1:

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';

回答2:

If I run your code:

SQL> CREATE OR REPLACE TRIGGER test
  2      AFTER UPDATE OF rgrupa
  3      ON table1
  4  BEGIN
  5      UPDATE table2 t2
  6         SET t2.rgrupa    = :new.rgrupa
  7       WHERE t2.id = :new.id;
  8  END;
  9  /
CREATE OR REPLACE TRIGGER test
                          *
ERROR at line 1:
ORA-04082: NEW or OLD references not allowed in table level triggers

Oracle clearly says that you have an issue in your code. If you want to use the :new, you need a row-level trigger:

SQL> CREATE OR REPLACE TRIGGER test
  2      AFTER UPDATE OF rgrupa
  3      ON table1
  4      FOR EACH ROW
  5  BEGIN
  6      UPDATE table2 t2
  7         SET t2.rgrupa    = :new.rgrupa
  8       WHERE t2.id = :new.id;
  9  END;
 10  /

Trigger created.