0

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?

5
  • 2
    If an exception is raised during a DML operation, why should that DML operation succeed? Oct 24 '20 at 18:40
  • 1
    the function does what it says it prevents deletion if status is A and parent exists. The trigger grabs the row before committing it, and then runs your checks and then would delete it, if you don't raise an exception
    – nbk
    Oct 24 '20 at 18:42
  • 1
    What is your goal for this trigger? Oct 24 '20 at 20:15
  • Even if you didn't have a misconception - I think you do - the "raise" would cause the transaction to rollback. I think the DB should remove the "relation" tuple before the "child" tuple. The relation removal fails in any case. Oct 25 '20 at 12:18
  • That makes sense now.
    – Ken Gondor
    Oct 26 '20 at 7:44
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.