级联删除到另一个表的行

iqjalb3h 于 2021-08-09 发布在 Java

关注(0)|答案(1)|浏览(403)

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

当我试图删除id=id\u capitalulo的“livro”时，我想删除“livro”和“capitalulo”，但是当删除“livro”时，它返回成功，但它不删除“livro”或“capitalulo”。。。我试图放置一个计数器，以防表中有超过1个“capitulo”，其id等于我请求删除时放置的id删除它

sql postgresql database-trigger foreign-keys sql-delete

来源：https://stackoverflow.com/questions/62353139/cascade-delete-to-row-of-another-table

1条答案

按热度按时间

62lalag41#

当我试图删除id=id\u capitalulo的“livro”时，我想删除“livro”和“capitalulo”
你不需要扳机。相反，您可以使用 on delete cascade 选项：

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

至于你现有的代码：因为你有一个 delete 触发器，需要使用伪表 old 而不是 new （实际上是空的）：

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

赞(0）回复(0）举报 2021-08-09

我来回答

级联删除到另一个表的行

1条答案

相关问题

热门标签

最新问答