Cascade DELETE to row of another table

CREATE OR REPLACE FUNCTION removerCapitulo()
  RETURNS trigger AS
$BODY$
declare counter int;
BEGIN
select count(*) into counter FROM capitulos where id_capitulo = NEW.id_livro;
if (counter >1)
THEN
DELETE FROM capitulos WHERE id_capitulo = NEW.id_livro;
END IF;
    RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER removerCapitulo_trigger
  BEFORE DELETE
  ON livros
  FOR EACH ROW
  EXECUTE PROCEDURE  removerCapitulo();

 DELETE FROM public.livros
    WHERE id_livro = 30

When i try to delete a “livro” with id = id_capitulo i want to delete the “livro” and the “capitulo”, however when deleting a “livro” it returns success, but it does not delete “livro” or “capitulo” … I tried to put a counter in case there is more than 1 “capitulo” in the table with an id equal to the id I put when I ask to delete it delete

Answer

When i try to delete a “livro” with id = id_capitulo i want to delete the “livro” and the “capitulo”

You don’t need a trigger for this. Instead, you can have a foreign key with the on delete cascade option:

create table capitulos (
    ...              -- other table columns
    id_capitulo int 
        references livros(id_livro)
        on delete cascade
);

---

As for your existing code: since you have a delete trigger, you need to use pseudo-table old rather than new (which is actually empty):

create or replace function remover_capitulo()
returns trigger as
$body$
begin
    delete from capitulos where id_capitulo = old.id_livro;
    return old;
end;
$body$
language plpgsql;

create trigger trg_remover_capitulo
    before delete on livros
    for each row
    execute procedure remover_capitulo();