Oracle SQL / PL/SQL: Insert if changed/not found; else do nothing -

totally reedited after thought. hoping combine separate merge statement, forget that. clear not upsert. want add new record if existing, matching record not found.

let's mytable holds columns foo, bar, baz. (if relevant performance sake) foo , bar uniquely identify record.

a) if not foo , bar insert foo, bar, baz

b) else, if foo , bar baz<new> not equal baz<old> insert foo, bar, baz<new>

c) else if 3 match nothing

note b likewise new record not update. log recording changes baz.

as added bonus, great if merge statement (to my_second_table) attempt merge on a , b since case c means baz unchanged therefore don't need touch other table. know can't have everything.

why can't use javascript mongo does...?

there no way update 2 tables in merge statement, there couple of alternatives. 1 adding trigger first table. trigger fire on change want , can insert rows in second table:

create trigger iua_table1 after insert or update on table1 each row begin    insert logtable(groupid, var, sku_no )   values(:new.groupid, var, sku_no);  end; 

this trigger fire on insert or update, not merge statement.

note people love triggers , use them anything, while others reluctant use them anything. think shouldn't put business logic in triggers, logging changes might acceptable.