pl/sql trigger

[misticawi]

i need some help i am a dba not a programmer.

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;

[akkerend]

Trigger to update table A after update or delete on table B:

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

[akkerend]

after an insert or update, not after update or delete.

[ssu4716]

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;

....

I hope you get the idea now.

Thanks.

Syed