SQL - Trigger with recursion

Question

I've got this problem with this exercise apologize that we insert into the table friends that at start is empty some couple of friends

Table Friends:

  +------------+------------+
  |  Friend1   |   Friend2  |
  +------------+------------+
  | Marc       | Luc        |    
  | Luc        | Marc       |        
  | Marc       | John       |
  | John       | Marc       |   
  +-------------------------+

The table Relationship at the beginning is empty

    +--------------------------------+-----------+---------+
    |   GradeOfRelationShip          |  Friend1  | Friend2 |
    +--------------------------------+-----------+---------+
    |                                |           |         |
    +--------------------------------+-----------+---------+

After the insert of the tuple of friends I'll see something like this

      +--------------------------------+-----------+---------+
      |   GradeOfRelationShip          |  Friend1  | Friend2 |
      +--------------------------------+-----------+---------+
      |          1                     |  Marc     |   Luc   |
      |          1                     |  Luc      |   Marc  |
      |          1                     |  Marc     |   John  |
      |          1                     |  John     |   Marc  |
      +--------------------------------+-----------+---------+

I inserted into Relationship only the couple of friends directly (in table Friends I insert only direct friends so with Grade 1)

Then I need to find all possible couple of friends from what I inserted into relationship so the output will be:

      +--------------------------------+-----------+---------+
      |   GradeOfRelationShip          |  Friend1  | Friend2 |
      +--------------------------------+-----------+---------+
      |          1                     |  Marc     |   Luc   |
      |          1                     |  Luc      |   Marc  |
      |          1                     |  Marc     |   John  |
      |          1                     |  John     |   Marc  |
      |          2                     |  Luc      |   John  |
      |          2                     |  John     |   Luc   |
      +--------------------------------+-----------+---------+

So what my trigger must do:

1. After the insert of a couple of friends into table Friends I need to check if this couple already exist into table relationship.

   • If exists with GradeOfRelationship = 0 I need to set this GradeOfRelationship with the new value = 1 (now I've got a path between this friends)
   • If not exists i need to insert this tuple into the table Relationship with GradeOfRelationship = 1

2. I must search all possible path between all friend that I've inserted into Relationship, for do that i think that it's necessary a Join like this

   Select 
       Table1.Friend1, Table1.Friend2, Table1.GradeOfRelationship,
       Table2.Friend1, Table2.Friend2, Table2.GradeOfRelationship
   From 
       Relationship Table1 
   join 
       Relationship Table2 on Table1.Friend2 = Table2.Friend1  
   

And the output that i think that I'll see:

      +-------------------+------+-----------+---------+-------+
      | Friend1  | Friend2| Lv   |  Friend1  | Friend2 | Lv    |
      +-------------------+------+-----------+---------+-------+
      |  Marc    |  Luc   |  1   |  Luc      |   Marc  |  1    |
      |  Marc    |  John  |  1   |  John     |   Marc  |  1    |
      |  John    |  Marc  |  1   |  Marc     |   Luc   |  1    |
      |  Luc     |  Marc  |  1   |  Marc     |   John  |  1    |
      +--------------------------------+-----------+-----------+

So I'll be interested only with the tuple in which

   Table1.Friend1 < > Table2.Friend2
   and Table1.Friend1 < > Table2.Friend2 not exist in RelationShip
   Insert into Table Relationship Values (Table1.Friend1,   
        Table2.Friend2, 
    Table1.GradeOfRelationship + Table2.GradeOfRelationship)

So now there is the main problem, the recursive function that after every insert on table Friends will do this. And the other problem is that i need to insert into GradeOfRelationship the minimum path between friends. If there is not any relationship between two people i must insert into table Relationship the couple of friends with grade 0

I trying with SQL Server but at the moment i don't have any idea to how do this statement, i think that i must use some recursive function but i don't know how....i tried to write CTE but it don't work like i want

   With Table1 as (Select Friend1,Friend2, 0 as Level
        from Friend
        Union All
        Select F1.Friend1, Table1.Friend2, Level+1 as FriendshipLevel
        From Friend F1 join table1 on F1.Friend2=table1.Friend1
        where F1.Friend1 < > Table1.Friend2
        ) 

    Select *
    From Table1

This SQL query didn't work well because it start to all possible computation with friends so 0 to infinite so it go on no termination.

Thanks a lot for your cooperation and looking forward into your answer

This is the SQL Schema of both table (Table Relationship is empty because it ll populate after the enter in action of the trigger)

  CREATE TABLE Friends
        (`Friend1` varchar(50), `Friend2` varchar(50))
  ;

  INSERT INTO Friends
       (`Friend1`, `Friend2`)
  VALUES
       ('Luc', 'Marc'),
       ('Marc', 'Luc'),
       ('John', 'Marc'),
       ('Marc', 'John'),
       ('Alex', 'Marc'),
       ('Marc', 'Alex')
  ;

  CREATE TABLE Relationship
    (`Friend1` varchar(50), `Friend2` varchar(50),`GradeOfRelationShip`  
     int )
    ;

Best Regards