"The multi-part identifier .... could not be found"

[SlurrerOfSpeech]

Microsoft SQL Server 2014 (T-SQL)

My codez are

CREATE DATABASE JsPracticeDb; 
/* Create tables corresponding to the problems, solutions to 
   problems, and ratings of problems or solutions */
CREATE TABLE Problems ( 
    id INT PRIMARY KEY IDENTITY(1,1) NOT NULL, 
    prompt_code VARCHAR(3000) NOT NULL,
    test_func_code VARCHAR(3000) NOT NULL,
    test_input_code VARCHAR(3000) NOT NULL,
    created DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE Solutions (
   id INT PRIMARY KEY IDENTITY(1,1) NOT NULL, 
   problem_id INT NOT NULL,
   solver VARCHAR(50),
   code VARCHAR(3000),
   FOREIGN KEY (problem_id) REFERENCES Problems(id) ON DELETE CASCADE,
   created DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE Ratings (
    id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
    solution_id INT NOT NULL,
    stars TINYINT NOT NULL,
    FOREIGN KEY (solution_id) REFERENCES Solutions(id) ON DELETE CASCADE
);
CREATE TABLE Comments ( 
    id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
    solution_id INT NOT NULL,
    commenter VARCHAR(50),
    comment VARCHAR(2000) NOT NULL,
    created DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (solution_id) REFERENCES Solutions(id) ON DELETE CASCADE
);
/* Closure Table for comment hierarchy */
CREATE TABLE CommentPaths (
    ancestor INT NOT NULL,
    descendant INT NOT NULL,
    PRIMARY KEY (ancestor, descendant),
    FOREIGN KEY (ancestor) REFERENCES Comments(id) ON DELETE CASCADE,
    FOREIGN KEY (descendant) REFERENCES Comments(id) ON DELETE CASCADE
);
/* Create trigger for deleting all comment descendants when 
   the comment is deleted */
GO
CREATE TRIGGER deleteDescendants ON CommentPaths FOR DELETE
AS
DELETE FROM Comments WHERE id=deleted.descendant;

and the very last part deleted.descendant is getting hit with the error

"The multi-part identifier deleted.descendant could not be bound."

Any help?

Also, is there anything wrong with the logic of my database setup, and there is any way of doing what I'm trying to do without using a trigger?

 

[Samy_A]

Microsoft SQL Server 2014 (T-SQL)

My codez are

CREATE DATABASE JsPracticeDb;
/* Create tables corresponding to the problems, solutions to
   problems, and ratings of problems or solutions */
CREATE TABLE Problems (
    id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
    prompt_code VARCHAR(3000) NOT NULL,
    test_func_code VARCHAR(3000) NOT NULL,
    test_input_code VARCHAR(3000) NOT NULL,
    created DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE Solutions (
   id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
   problem_id INT NOT NULL,
   solver VARCHAR(50),
   code VARCHAR(3000),
   FOREIGN KEY (problem_id) REFERENCES Problems(id) ON DELETE CASCADE,
   created DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE Ratings (
    id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
    solution_id INT NOT NULL,
    stars TINYINT NOT NULL,
    FOREIGN KEY (solution_id) REFERENCES Solutions(id) ON DELETE CASCADE
);
CREATE TABLE Comments (
    id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
    solution_id INT NOT NULL,
    commenter VARCHAR(50),
    comment VARCHAR(2000) NOT NULL,
    created DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (solution_id) REFERENCES Solutions(id) ON DELETE CASCADE
);
/* Closure Table for comment hierarchy */
CREATE TABLE CommentPaths (
    ancestor INT NOT NULL,
    descendant INT NOT NULL,
    PRIMARY KEY (ancestor, descendant),
    FOREIGN KEY (ancestor) REFERENCES Comments(id) ON DELETE CASCADE,
    FOREIGN KEY (descendant) REFERENCES Comments(id) ON DELETE CASCADE
);
/* Create trigger for deleting all comment descendants when
   the comment is deleted */
GO
CREATE TRIGGER deleteDescendants ON CommentPaths FOR DELETE
AS
DELETE FROM Comments WHERE id=deleted.descendant;

and the very last part deleted.descendant is getting hit with the error

"The multi-part identifier deleted.descendant could not be bound."

Any help?

Also, is there anything wrong with the logic of my database setup, and there is any way of doing what I'm trying to do without using a trigger?

In a delete trigger, the virtual table "deleted" can contain more than one record, that's why

DELETE FROM Comments WHERE id=deleted.descendant;

doesn't work.
Try something like

DELETE FROM Comments WHERE id in (select deleted.descendant from deleted);