触发从相关表中删除行，然后再从实际表中删除行 [英] Trigger to delete rows from related tables before deleting rows from actual table

问题描述

我有下表:

CREATE TABLE QUESTION(
id varchar(10) NOT NULL PRIMARY KEY,
que_type numeric(1));

CREATE TABLE ESSAY(
que_id varchar(10) NOT NULL PRIMARY KEY,
ans varchar(2000),
FOREIGN KEY (que_id) REFERENCES QUESTION (id));

CREATE TABLE TFFB(
que_id varchar(10) NOT NULL PRIMARY KEY,
ans varchar(50),
FOREIGN KEY (que_id) REFERENCES QUESTION (id));

CREATE TABLE MCQ(
que_id varchar(10) NOT NULL PRIMARY KEY,
ans varchar(200),
FOREIGN KEY (que_id) REFERENCES QUESTION (id));

并尝试创建触发器，以便当我从主表中删除时，它将从其他表中删除相关行:

and try to create trigger so that when I delete from the main table, it will delete related rows from other tables:

CREATE OR REPLACE FUNCTION delete_question()
RETURNS trigger AS $delete_question$
DECLARE
    BEGIN
    IF ( (OLD).que_type = '1' ) THEN
       IF EXISTS (SELECT 1 FROM mcq WHERE person_id = (OLD).id) THEN
          DELETE FROM mcq WHERE que_id='(OLD).id';
       END IF;
    ELSIF ( (OLD).que_type = '2' OR OLD.que_type = '3' ) THEN
       IF EXISTS (SELECT 1 FROM tffb WHERE person_id = (OLD).id) THEN
          DELETE FROM tffb WHERE que_id='(OLD).id';
       END IF;
    ELSIF ( (OLD).que_type = '4' ) THEN
       IF EXISTS (SELECT 1 FROM essay WHERE person_id = (OLD).id) THEN
          DELETE FROM essay WHERE que_id='(OLD).id';
       END IF;
    END IF;
    RETURN NULL;
    END;
    $delete_question$ LANGUAGE plpgsql;

    CREATE TRIGGER delete_question
    BEFORE DELETE ON question
        FOR EACH ROW EXECUTE PROCEDURE delete_question();

当我从question删除数据时，该行消失了一段时间.但是当我刷新时，它仍然存在.
我尝试放入RETURN OLD;，但由于约束关系而失败. 怎么了?

When I delete data from question, the row disappear for a while. But when I refresh, it still there.
I tried to put RETURN OLD; but it failed because of the constrain relation. What is wrong with this?

推荐答案

关于触发函数的更多建议:

Some more advice on your trigger function:

CREATE OR REPLACE FUNCTION delete_question()
  RETURNS trigger AS
$func$
BEGIN

CASE OLD.que_type
WHEN 1 THEN
    DELETE FROM mcq   WHERE que_id=OLD.id;
WHEN 2, 3 THEN
    DELETE FROM tffb  WHERE que_id=OLD.id;
WHEN 4 THEN
    DELETE FROM essay WHERE que_id=OLD.id;
-- ELSE
--      Do something?
END CASE;

RETURN OLD;

END
$func$ LANGUAGE plpgsql;

要点

• 使用SELECT语句检查是否存在会使成本加倍.只需运行DELETE，如果找不到匹配的行，则不会删除任何内容.

  Major points

  • Your check for existence with a SELECT statement doubles the cost. Just run the DELETE, if no matching row is found, nothing is deleted.

    在此处使用CASE语句.更短，更快.请注意，plpgsql CASE与SQL CASE语句略有不同.例如，您可以一次列出几个案例.

    Use a CASE statement here. Shorter, faster. Note that plpgsql CASE is slightly different from SQL CASE statement. For instance, you can list several cases at once.

    除非实际声明变量，否则不需要DECLARE关键字.

    You don't need the DECLARE keyword, unless you actually declare variables.

    您可以通过来完全避免该问题.通过外键级联删除，如

    You could avoid the problem altogether by cascading deletes via foreign key, as @a_horse mentioned in the comment. My schema layout would look like this:

    CREATE TABLE question (
       question_id serial NOT NULL PRIMARY KEY
      ,que_type    int   -- this may be redundant as well
    );
    
    CREATE TABLE essay (
       que_id int NOT NULL PRIMARY KEY
                  REFERNECES question(question_id) ON UPDATE CASCADE
                                                   ON DELETE CASCADE
      ,ans    text
    );
    
    ...
    

    关于serial:
    自动递增SQL函数

    这篇关于触发从相关表中删除行，然后再从实际表中删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！