1

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
  • 605,456
  • 145
  • 1,078
  • 1,228
Archmal
  • 63
  • 1
  • 8
  • 3
    What's wrong with using `ON DELETE CASCADE` for the foreign keys? –  Nov 18 '13 at 15:36

2 Answers2

2

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

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
1

i then try solve it again.... IT WORKS PERFECTLY. YEAY.

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 que_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 que_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 que_id=OLD.id) THEN
      DELETE FROM essay WHERE que_id=OLD.id;
   END IF;
END IF;
RETURN OLD;
END;
$delete_question$ LANGUAGE plpgsql;

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

there is no ' ' on OLD.id or even (OLD).id and use RETURN OLD;

Archmal
  • 63
  • 1
  • 8