I ran into some problems in using triggers to ensure only deletions from cascading deletes:
CREATE TYPE status_type AS enum ('A');
CREATE TABLE parent(
name VARCHAR(255) PRIMARY KEY
);
CREATE TABLE child(
name VARCHAR(255) PRIMARY KEY
);
CREATE TABLE relation(
childname VARCHAR(255) NOT NULL REFERENCES child (name) ON DELETE CASCADE,
parentname VARCHAR(255) NOT NULL REFERENCES parent (name) ON DELETE CASCADE,
status status_type NOT NULL,
PRIMARY KEY (childname, parentname)
);
CREATE OR REPLACE FUNCTION prevent_deletes_when_status_A() RETURNS trigger AS $$
BEGIN
IF OLD.status='A' AND EXISTS (SELECT 1 FROM parent WHERE parent.name=OLD.parentname) THEN
RAISE EXCEPTION 'Invalid DELETE';
ELSE
RETURN OLD;
END IF;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER tr_onlycascading_fromParent
BEFORE DELETE ON relation
FOR EACH ROW EXECUTE PROCEDURE prevent_deletes_when_status_A();
INSERT into parent VALUES ('john');
INSERT into child VALUES ('doe');
INSERT into relation VALUES ('doe', 'john', 'A');
DELETE FROM child WHERE name='doe';
Why does this not work? I understand that it raises the exception message. However, the row in child should be deleted. Am I missing something here?
Shouldn't the row in child be deleted first before the delete cascades to the relation table, where only then would the trigger take effect?