Understanding Oracle Triggers and Self-Referential Tables

In this article, we will delve into the world of Oracle triggers and self-referential tables. Specifically, we will explore how to define a trigger that inserts one more row into the same table after each insert, while avoiding infinite loops.

Introduction to Oracle Triggers

An Oracle trigger is a stored procedure that fires automatically before or after certain database actions, such as inserting, updating, or deleting data. In this case, we want to create a trigger that inserts one more row into the same table after each insert.

Creating a Self-Referential Table

A self-referential table is a table where a column contains references to rows within the same table. In our example, we have a table m_to_n with columns ID_1 and ID_2, which represent one side of a many-to-many relationship.

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

The Problem with the Original Trigger

The original trigger provided in the Stack Overflow question is not correct for several reasons:

• It creates an infinite loop of inserts because it inserts a new row into the same table after each insert.
• When ID_1 = ID_2, the primary key constraint prevents inserting a second row, which means that even if the trigger were to work, it would still fail on this edge case.

The Solution: Normalizing the Table Structure

To avoid these issues, we need to normalize the table structure. Specifically, we should make the reflexive nature of the data part of the table structure only capturing one side of the relationship.

CREATE TABLE m_to_n (
    ID_1 NUMBER(9,0),
    ID_2 NUMBER(9,0),
    PRIMARY KEY (ID_1, ID_2),
    CONSTRAINT id_1__lte__id_2 CHECK (id_1 <= id_2)
);

In this normalized structure, we have removed the duplicate column ID_1 and instead added a constraint to ensure that ID_1 is less than or equal to ID_2.

Joining the Table

When joining the table, we can include both the forward and backward directions in the join.

SELECT *
FROM   parent p
       CROSS JOIN child c
       INNER JOIN m_to_n mn
       ON ( p.id = mn.id_1 AND c.id = mn.id_2 )
          OR ( p.id = mn.id_2 AND c.id = mn.id_1 );

Understanding the CROSS JOIN

In SQL, a CROSS JOIN is used to combine each row of one table with every row of another table. This results in a Cartesian product of the two tables.

SELECT * FROM parent p
       CROSS JOIN child c;

The Role of the INNER JOIN

An INNER JOIN is used to join rows between two tables based on a common column. In our example, we use an INNER JOIN to match rows between parent, child, and m_to_n.

SELECT * FROM parent p
       INNER JOIN m_to_n mn
       ON ( p.id = mn.id_1 AND c.id = mn.id_2 )
          OR ( p.id = mn.id_2 AND c.id = mn.id_1 );

Conclusion

In this article, we explored the world of Oracle triggers and self-referential tables. We discussed how to define a trigger that inserts one more row into the same table after each insert, while avoiding infinite loops.

We also normalized the table structure to make the reflexive nature of the data part of the table structure only capturing one side of the relationship.

Finally, we joined the table using both CROSS JOIN and INNER JOIN. By following these steps, you can create a robust and efficient database design for your many-to-many relationships.

Last modified on 2023-11-21