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
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();