SQL Language (MOSC)

MOSC Banner

merge statement in trigger

edited May 6, 2014 12:28PM in SQL Language (MOSC) 3 commentsAnswered ✓

I created table ra.test1(a number, b varchar2(10)) and table ra.test2(a number, b varchar2(10))

Try to sync two tables using merge statement in trigger

RA@mainpro2 > CREATE OR REPLACE TRIGGER ra.trai_test1

  2  AFTER insert ON ra.test1

  3  FOR EACH ROW

  4  DECLARE

  5 

  6 

  7  v_a number;

  8  v_b varchar2(10);

  9 

10  BEGIN

11 

12  v_a := :new.a;

13  v_b := :new.b;

14 

15   merge INTO test2 t

16       USING ( select v_a, v_b from dual

17        ) s

18       ON ( s.v_a = t.a)

19       WHEN matched THEN UPDATE SET

20         t.a  = s.v_a

21         , t.b  = s.v_b

22       WHEN NOT MATCHED THEN INSERT

23       (

24         a

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center