0

Totally reedited this after some thought. Originally I was hoping to combine with a separate MERGE statement, but forget that. To be clear this is not an upsert. I want to add a new record if an existing, matching record is not found.

Let's say mytable holds columns foo, bar, baz. Where (if relevant for performance sake) foo and bar together uniquely identify the record.

a) If not foo and bar then insert foo, bar, baz

b) Else, if foo and bar but baz<new> does not equal baz<old> insert foo, bar, baz<new>

c) Else if all three match then do nothing

Note that b is likewise a new record not an update. Essentially this is a log recording changes to baz.

As an added bonus, it would be great if I could get the MERGE statement (to my_second_table) to only attempt a merge on a and b since case c means baz is unchanged therefore we don't need to touch the other table. But I know you can't have everything.

Why can't they just use JavaScript like Mongo does...?

Peter
  • 95
  • 1
  • 8
  • There is no PL/SQL in your example –  May 31 '15 at 17:30
  • I just figured a PL/SQL procedure what I'd need to the extend this to do what I need it to, no? At least this is the direction googling has led me. – Peter Jun 01 '15 at 13:21

1 Answers1

0

There is no way to update two tables in a merge statement, but there are a couple of alternatives. One is adding a trigger to the first table. The trigger will fire on any change you want and can insert rows in the second table:

CREATE TRIGGER IUA_Table1
AFTER INSERT OR UPDATE
ON Table1 FOR EACH ROW
BEGIN

  INSERT INTO LogTable(groupid, var, sku_no )
  VALUES(:new.groupid, var, sku_no);

END;

This trigger will fire on any insert or update, so not just your merge statement.

Note that some people love triggers and use them for most anything, while others are very reluctant to use them for anything. Personally I think you shouldn't put too much business logic in triggers, but for logging changes like this it might be acceptable.

GolezTrol
  • 114,394
  • 18
  • 182
  • 210