Unable to delete a referenced row due to a trigger on a referencing table?

Question

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?

Answer 1

All the cascading deletes and your trigger function are running inside the same database transaction, so if the transaction is aborted by an error you raise in the trigger function, all actions that took place in the same transaction (including the triggering delete) are rolled back.