-
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;
-
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;
-
after an insert or update, not after update or delete.
-
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