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?