Trigger to delete rows from related tables before deleting rows from actual table

Archmal

I have the following tables:

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

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?

Erwin Brandstetter

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;

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.

  • 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.

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

Alternative design

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

...

About serial:
Auto increment SQL function

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Java

Deleting all rows from Cassandra cql table

From Dev

delete rows from a table using MySQL Scheduler

From Dev

Deleting multiple rows from a table

From Dev

Delete rows from different tables in CodeIgniter

From Dev

Better "delete rows from table" performance

From Dev

Deleting rows from 3 tables in MySQL table

From Dev

Delete rows from table

From Dev

Delete rows from two tables related

From Dev

Deleting rows from multiple tables with jQuery

From Dev

PostgreSQL: deleting rows referenced from another table

From Dev

Delete duplicate rows from table with no unique key

From Dev

Delete rows from table recursively using JQ

From Dev

Delete rows from multiple tables as one query

From Dev

Delete Rows from a Constrained Reference Table

From Dev

Postgresql delete multiple rows from multiple tables

From Dev

Trying to delete selected row from datagridview but it is deleting multiple rows

From Dev

Delete rows from all tables

From Dev

Delete duplicate rows from a BigQuery table

From Dev

Count rows from a related table

From Dev

Delete rows from multiple tables in a database

From Dev

Deleting multiple rows from a table

From Dev

Delete X rows from table

From Dev

SQL, How to delete rows from related tables using a query?

From Dev

Deleting related rows from other tables without primary key

From Dev

Delete few rows from db table

From Dev

Issue deleting rows from displayed table

From Dev

Deleting rows from two tables related by constraint

From Dev

DELETE rows from multiple tables with JOIN in Mysql

From Dev

Combine rows from Mulitple tables into single table