If a delete trigger is fired on a table due to an ON DELETE CASCADE action, will the trigger see the rows in the parent table which triggered the cascade? Will a trigger on the originating table see rows in the child tables? Does it matter if the trigger is a “before” or an “after” trigger? The answer to these questions was not immediately obvious to me, and my half-minute of searching didn’t find a clear answer, so I have written this post to remind myself and others what happens in PostgreSQL 9.1.

Test Script

In order to test the behavior, I wrote the following test script. It simply creates a parent table, child table, results table, and a trigger which fires on both the parent and child to record whether the parent row being deleted is present in the parent table.

CREATE TABLE parents (
    parent_id INTEGER NOT NULL PRIMARY KEY
);

CREATE TABLE children (
    child_id INTEGER NOT NULL PRIMARY KEY,
    parent_id INTEGER NOT NULL REFERENCES parents(parent_id)
	ON DELETE CASCADE
	ON UPDATE CASCADE
);

CREATE TABLE results (
    result_id SERIAL PRIMARY KEY,
    table_name VARCHAR(10) NOT NULL,
    trigger_when VARCHAR(10) NOT NULL,
    parent_present BOOLEAN NOT NULL,
    children_present INTEGER NOT NULL
);

CREATE OR REPLACE FUNCTION report_parent_id() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO results (table_name, trigger_when, parent_present, children_present)
    VALUES (
	TG_TABLE_NAME,
	TG_WHEN,
	EXISTS (SELECT 1 FROM parents WHERE parent_id = OLD.parent_id),
	(SELECT COUNT(*) FROM children WHERE parent_id = OLD.parent_id)
    );

    RETURN OLD;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER tr_parents_bd_report_parent_id
BEFORE DELETE ON parents
FOR EACH ROW EXECUTE PROCEDURE report_parent_id();
CREATE TRIGGER tr_parents_ad_report_parent_id
AFTER DELETE ON parents
FOR EACH ROW EXECUTE PROCEDURE report_parent_id();
CREATE TRIGGER tr_children_bd_report_parent_id
BEFORE DELETE ON children
FOR EACH ROW EXECUTE PROCEDURE report_parent_id();
CREATE TRIGGER tr_children_ad_report_parent_id
AFTER DELETE ON children
FOR EACH ROW EXECUTE PROCEDURE report_parent_id();

INSERT INTO parents (parent_id) VALUES (1);
INSERT INTO children (child_id, parent_id) VALUES (1, 1);
INSERT INTO children (child_id, parent_id) VALUES (2, 1);
DELETE FROM parents;

SELECT * FROM results;

What Happens

Ok, here’s the answer: The parent row is not visible from either before or after triggers on the child table. It is visible in the before trigger on the parent table but not the after trigger (as expected/documented). The child rows are visible in all before triggers (showing decreasing numbers in the child trigger as child rows are deleted, as expected/documented) and no after triggers Which changes are visible during cascading updates is left as an exercise for the interested reader.