Delete Cascade with Trigger and Procedures PostgreSQL.

cascade_delete_with_procedures_postgres.sql

CREATE TABLE "users" (
	"id" SERIAL PRIMARY KEY,
  "name" TEXT NOT NULL
);

CREATE TABLE "files" (
  "id" SERIAL PRIMARY KEY,
  "name" text NOT NULL
);

CREATE TABLE "recipes" (
  "id" SERIAL PRIMARY KEY,
  "user_id" INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  "title" TEXT NOT NULL
);

-- PIVOT TABLE (Tabela Pivô usada para ligar duas tabelas)
CREATE TABLE "recipe_files" (
  "id" SERIAL PRIMARY KEY,
  "recipe_id" integer REFERENCES recipes(id) 
  	ON DELETE CASCADE, -- quando deletar receita, deletar essa entrada
  "file_id" integer REFERENCES files(id)
);

-- Quero deletar os arquivos da receita que foi removida
-- Então vamos criar uma procedure para deletar o arquivo
CREATE OR REPLACE FUNCTION delete_files_when_recipe_files_row_was_deleted()
RETURNS TRIGGER AS $$
BEGIN
   -- executamos o comando de remoção
   EXECUTE ('DELETE FROM files
   WHERE id = $1') 
   USING OLD.file_id; -- OLD é a referencia da coluna de recipe_files que foi removido
   RETURN NEW; -- Retornando a entrada de recipe_files
END;
$$ LANGUAGE plpgsql;

-- e vamos criar uma triiger que vai disparar a procedure de remoção, 
-- depois que (AFTER) for deletada uma entrada em recipe_files
CREATE TRIGGER delete_recipe_files
AFTER DELETE ON recipe_files
FOR EACH ROW
EXECUTE PROCEDURE delete_files_when_recipe_files_row_was_deleted();


-- cadastrar usuário
INSERT INTO users (name) VALUES ('mayk');

-- cadastrar receitas do usuário acima
INSERT INTO recipes (user_id, title) VALUES (1, 'receita 01');
INSERT INTO recipes (user_id, title) VALUES (1, 'receita 02');

-- cadastrar arquivos
INSERT INTO files(name) VALUES ('imagem.jpg');
INSERT INTO files(name) VALUES ('imagem2.jpg');
INSERT INTO files(name) VALUES ('imagem3.jpg');

-- cadastrar relacionamento com a tabela pivo
INSERT INTO recipe_files(recipe_id, file_id) VALUES(1, 1);
INSERT INTO recipe_files(recipe_id, file_id) VALUES(1, 2);
INSERT INTO recipe_files(recipe_id, file_id) VALUES(2, 3);

-- ao remover o usuário, todas suas receitas deverão ser deletadas,
-- assim como entradas na tabela pivot e, também,
-- os arquivos das receitas
DELETE FROM users WHERE id = 1; 

-- apagar as tabelas pra recomeçar
DROP TABLE recipe_files;
DROP TABLE recipes;
DROP TABLE users;
DROP TABLE files;