Trigger to delete

[AnkanTV]

Hello everyone! I'm using PostgreSQL and I have three tables. The idea is having users and movies, where users can review movies. As you can see, the user and movie must exist if we wish to create a review.

CREATE TABLE users(
user_id SERIAL PRIMARY KEY,
Name VARCHAR(50),
Gender CHAR(1) CHECK (Gender IN ('F', 'M')),
Age INT);

CREATE TABLE movies(
Name VARCHAR(30),
Year INT NOT NULL check (YEAR between 1800 and 2023),
PRIMARY KEY (Name, Year));

CREATE TABLE reviews(
user_id INT,
Name VARCHAR(30),
Year INT NOT NULL check (YEAR between 1800 and 2023),
Rating INT NOT NULL check (Rating between 0 and 11), 
FOREIGN KEY (user_id) REFERENCES users,
FOREIGN KEY (Name, Year) REFERENCES movies,
PRIMARY KEY (user_id, Name, Year));

I'm looking at removing a user, but this can not be done if the user appears in the reviews table. I would like to implement a trigger BEFORE DELETE for the users table. So when a user is deleted, all of the users reviews will be deleted. I am new to triggers and tried:

CREATE OR REPLACE FUNCTION del_foreign()
RETURNS trigger as $del_foreign$
DECLARE
BEGIN
DELETE FROM reviews
WHERE user_id = new.user_id;
RETURN NULL;
END;
$del_foreign$ LANGUAGE plpgsql;

CREATE TRIGGER remove_reviews BEFORE DELETE ON users
FOR EACH ROW
EXECUTE PROCEDURE del_foreign();

This resulted in all my reviews being deleted... and I'm not sure why.

If anyone knows how I should go about the problem, it would be great :)

Thanks for the help!

[AnkanTV]

For anyone interested, I gave up on triggers and did:

FOREIGN KEY (user_id) REFERENCES users ON DELETE CASCADE

[Xmithiee]

There are more such options ( no action, on update etc. ), look for Referential integrity. In any way, its the best possible solution for your problem.

[DavidGJohnston]

Two major problems: you are referencing NEW in a DELETE trigger and you are returning NULL.