If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

i am creating a trigger that will update table a after an insert or update . this trigger will update 2 columns to equal table b when table a primary key = table b primary key.

create or replace trigger test
after insert or update on table a
for each row
begin
update table a
set tablea.column1 = tableb.column1,
tablea.column2 = tableb.column2
where tablea.idno = tableb.idno
end;

This is potentially a dangerous piece of code since this will work recursively (fire the trigger from inside the trigger).

What you should do is write a before insert/update trigger and set the new value for the columns, e.g.
...
v_column1 := NULL;
v_column2 := NULL;
select column1, column2
into v_column1, v_column2
from tableb
where idno = :new.idno;

IF v_column1 IS NOT NULL THEN
:new.column1 := v_column1;
END IF;
IF v_column2 IS NOT NULL THEN
:new.column2 := v_column2;
END IF;