Delete Query

USE tempdb;

CREATE TABLE dbo.Students( 
StudentID int NOT NULL IDENTITY PRIMARY KEY, 
FirstName varchar(10) NOT NULL, 
LastName varchar(10) NOT NULL
);
GO

CREATE PROCEDURE dbo.up_DropStudent( 
@StudentID int 
) 
AS 
DELETE dbo.Students 
WHERE StudentID = @StudentID;
GO

INSERT dbo.Students VALUES ('Lorenzo', 'Benaglia');
INSERT dbo.Students VALUES ('Luca', 'Bianchi');
INSERT dbo.Students VALUES ('Andrea', 'Montanari');

/* Elimino lo studente Lorenzo Benaglia */
EXEC dbo.up_DropStudent 1;

/* Verifica */
SELECT *
FROM dbo.Students;

/* Output:

StudentID   FirstName  LastName
----------- ---------- ----------
2           Luca       Bianchi
3           Andrea     Montanari

(2 row(s) affected)

*/

/* Pulizia */
DROP PROCEDURE dbo.up_DropStudent;
DROP TABLE dbo.Students;

USE tempdb;

CREATE TABLE dbo.Students( 
StudentID int NOT NULL PRIMARY KEY, 
FirstName varchar(10) NOT NULL, 
LastName varchar(10) NOT NULL,
BossID int NULL
);
GO

CREATE TRIGGER dbo.trD_Students
ON dbo.Students 
FOR DELETE
AS
DELETE dbo.Students
FROM dbo.Students AS S
JOIN DELETED AS D
ON S.BossID = D.StudentID;
GO

CREATE PROCEDURE dbo.up_DropStudent( 
@StudentID int 
) 
AS 
DELETE dbo.Students 
WHERE StudentID = @StudentID;
GO

INSERT dbo.Students VALUES (1, 'Lorenzo', 'Benaglia', NULL);
INSERT dbo.Students VALUES (2, 'Luca', 'Bianchi', 1);
INSERT dbo.Students VALUES (3, 'Andrea', 'Montanari', 1);
INSERT dbo.Students VALUES (4, 'Andrea', 'Benedetti', 2);
INSERT dbo.Students VALUES (5, 'Gianluca', 'Hotz', 2);

/* Elimino lo studente Lorenzo Benaglia e tutti i suoi "sudditi" :-D */
EXEC dbo.up_DropStudent 1;

/* Verifica */
SELECT *
FROM dbo.Students;

/* Output:

StudentID   FirstName  LastName   BossID
----------- ---------- ---------- -----------
4           Andrea     Benedetti  2
5           Gianluca   Hotz       2

(2 row(s) affected)

*/

/* Pulizia */
DROP PROCEDURE dbo.up_DropStudent;
DROP TABLE dbo.Students;

USE tempdb;

CREATE TABLE dbo.Students( 
StudentID int NOT NULL PRIMARY KEY, 
FirstName varchar(10) NOT NULL, 
LastName varchar(10) NOT NULL,
BossID int NULL
);
GO

CREATE PROCEDURE dbo.up_DropStudent( 
@StudentID int 
) 
AS 
WITH CTE_Students (StudentID, FirstName, LastName, BossID, Level)
AS
(
    /* Anchor member definition */
    SELECT *, 0 AS Level
    FROM dbo.Students
    WHERE StudentID = @StudentID

    UNION ALL

    /* Recursive member definition */
    SELECT S.*, C.Level + 1
    FROM dbo.Students AS S
    JOIN CTE_Students AS C
    ON S.BossID = C.StudentID
)
DELETE dbo.Students
FROM dbo.Students AS S
JOIN CTE_Students AS C
ON S.StudentID = C.StudentID;
GO

INSERT dbo.Students VALUES (1, 'Lorenzo', 'Benaglia', NULL);
INSERT dbo.Students VALUES (2, 'Luca', 'Bianchi', 1);
INSERT dbo.Students VALUES (3, 'Andrea', 'Montanari', 1);
INSERT dbo.Students VALUES (4, 'Andrea', 'Benedetti', 2);
INSERT dbo.Students VALUES (5, 'Gianluca', 'Hotz', 2);
INSERT dbo.Students VALUES (6, 'Marcello', 'Poletti', 5);

/* Elimino lo studente Luca Bianchi e tutti i suoi "sudditi" :-D */
EXEC dbo.up_DropStudent 2;

/* Verifica */
SELECT *
FROM dbo.Students;

/* Output:

StudentID   FirstName  LastName   BossID
----------- ---------- ---------- -----------
1           Lorenzo    Benaglia   NULL
3           Andrea     Montanari  1

(2 row(s) affected)

*/

/* Pulizia */
DROP PROCEDURE dbo.up_DropStudent;
DROP TABLE dbo.Students;