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?
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;
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.
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.
Comments