Oracle: How to define a trigger that inserts one more row into the same table after each insert?

Question

Say I have a m to n table like this, which makes entries from a table tab reference other entries in the same table:

CREATE TABLE m_to_n (
 ID_1 NUMBER(9,0),
 ID_2 NUMBER(9,0),
 PRIMARY KEY (ID_1, ID_2)
);

Now I would like to make a trigger that fires after every insert and inserts 1 new row into the same table with the ids reversed. For example:

CREATE OR REPLACE TRIGGER m_to_n 
AFTER INSERT
   ON m_to_n 
   FOR EACH ROW
BEGIN
    INSERT INTO m_to_n 
   ( ID_1,
     ID_2 )
   VALUES
   ( :new.ID_2,
     :new.ID_1 );

END;

For example: If an entry in tab with ID 1 references another with ID 3 then I insert in m_to_n: 1, 3

And the trigger should insert: 3, 1

My problem is: How can I do this without creating an infinite loop of inserts? Thanks very much.

Answer 1

While I completely agree with MT0's frame challenge, if you really did want to try to maintain the table like this then as well as infinite loops and matching ID 1/2 values, you would also get a mutating table error from a normal trigger.

Partly for fun... you could avoid the infinite loop by keeping track of whether you are already inside the trigger, e.g. using context - i this example dbms_application_info; and the mutation problem by using a compound trigger, something like:

CREATE OR REPLACE TRIGGER m_to_n_trg
FOR INSERT ON m_to_n
COMPOUND TRIGGER

   TYPE t_row IS RECORD (id_1 m_to_n.id_1%type, id_2 m_to_n.id_2%type);
   -- coudl also use table of m_to_n%ROWTYPE in this case...
   TYPE t_rows IS TABLE OF t_row;
   l_rows t_rows := t_rows();
   l_fixed_info CONSTANT VARCHAR2(30) := 'compound trigger hack';

   AFTER EACH ROW IS
      l_info VARCHAR2(30);
   BEGIN
      dbms_application_info.read_client_info(l_info);
      IF (l_info IS NULL OR l_info != l_fixed_info)
        AND :new.id_1 != :new.id_2 THEN
        -- not in recursive insert; store switched values
        l_rows.extend;
        l_rows(l_rows.COUNT).id_1 := :new.id_2;
        l_rows(l_rows.COUNT).id_2 := :new.id_1;
      END IF;
   END AFTER EACH ROW;

   AFTER STATEMENT IS
      l_old_info VARCHAR2(30);
   BEGIN
      -- store existing value to restore later
      dbms_application_info.read_client_info(l_old_info);

      -- set info to block recursion  
      dbms_application_info.set_client_info(l_fixed_info);

      -- insert update table based on all old/new value pairs at once
      FORALL i IN 1..l_rows.COUNT
         INSERT INTO m_to_n (id_1, id_2)
         VALUES (l_rows(i).id_1, l_rows(i).id_2);

      -- reset info
      dbms_application_info.set_client_info(l_old_info);
   END AFTER STATEMENT;
END;
/

Then after a few inserts:

insert into m_to_n (id_1, id_2) values (1, 2);
insert into m_to_n (id_1, id_2) values (4, 3);
insert into m_to_n (id_1, id_2) values (5, 5);

You end up with:

select * from m_to_n;

ID_1	ID_2
1	2
2	1
3	4
4	3
5	5

fiddle

But this does not seem like the best approach, not least because you're duplicating data unnecessarily, and adding complexity to eh data model that you don't seem to really need.