I’ve been assigned a task to delete data from a table with a subtree structure which means the foreign key can be contained in another rows in the same table. It’s a self contained foreign key on the same table.

Theoritically when it comes to my mind, i was thinking of recursive delete. But how????I’ve tried to google about it, I found this article from Microsoft. These are coming from Microsoft Article as well.

These are the dummy data that you can play around with in your DB

CREATE TABLE Employees
(empid int NOT NULL,
 mgrid int NULL,
 empname varchar(25) NOT NULL,
 salary money NOT NULL,
 CONSTRAINT PK_Employees_empid PRIMARY KEY(empid))

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES( 1, NULL, 'Nancy',  $10000.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES( 2,    1, 'Andrew',  $5000.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES( 3,    1, 'Janet',   $5000.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES( 4,    1, 'Margaret',$5000.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES( 5,    2, 'Steven',  $2500.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES( 6,    2, 'Michael', $2500.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES( 7,    3, 'Robert',  $2500.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES( 8,    3, 'Laura',   $2500.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES( 9,    3, 'Ann',     $2500.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES(10,    4, 'Ina',     $2500.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES(11,    7, 'David',   $2000.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES(12,    7, 'Ron',     $2000.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES(13,    7, 'Dan',     $2000.00)

INSERT INTO employees(empid, mgrid, empname, salary)
  VALUES(14,   11, 'James',   $1500.00)

I’m going to delete employee no 3(Janet) which means it need to delete 7(Robert),8(Laura),9(Ann) and 11(David),12(Ron),13(Dan), 14(James) based on the managerial structure level.

What you need to do is to create a trigger for cascading delete

CREATE TRIGGER trg_d_employees_on_delete_cascade ON Employees FOR DELETE
AS

IF EXISTS(SELECT *
          FROM
              Employees AS E
            JOIN
              deleted   AS D ON E.mgrid = D.empid)

  DELETE FROM Employees
  FROM
      Employees AS E
    JOIN
      deleted   AS D ON E.mgrid = D.empid

  GO

It will self join based on the deleted table. It’s not finish yet, you need to set the recursive trigger to active by executing command like

ALTER DATABASE DMO
SET RECURSIVE_TRIGGERS ON

Action Time:

DELETE FROM employees WHERE empid = 3

and you will see that it deletes all the subordinates of Janet