Hello Achilleus
Thanks for your feedback. On changing the return to NULL:
According to the docs, if I return NULL in the BEFORE trigger itself, all
subsequent triggers and the row-level op itself (the actual delete) will be
skipped completely, which is no good. I will confirm this to make sure
though.
On your suggestion of manually updating, I have been trying something like
this with interesting (but undesirable!) results:
CREATE OR REPLACE FUNCTION fn_trg_mark_ref_as_deleted() RETURNS TRIGGER AS
$$
BEGIN
UPDATE ref_table SET deleted = TRUE
WHERE ref_id = OLD.ref_id;
RETURN OLD;
END;
$$
LANGUAGE 'plpgsql' VOLATILE;
CREATE TRIGGER mark_ref_as_deleted BEFORE DELETE ON ref_table
FOR EACH ROW EXECUTE PROCEDURE fn_trg_mark_ref_as_deleted();
(I'm returning OLD for the reason above). Oddly, this does indeed set the
soft-delete flag but never deletes the row, even if there are no constraint
dependencies.
I'm going to keep playing but any other suggestions would be very welcome :)
Here are some sample schema and defs for anyone who's interested:
----------------------------------------------------------------------------
--------
-- SCHEMA DEFS:
BEGIN;
CREATE TABLE ref_table
(
ref_id INTEGER NOT NULL,
deleted BOOLEAN DEFAULT FALSE,
CONSTRAINT ref_table_pkey PRIMARY KEY (ref_id)
);
CREATE TABLE dep_table
(
dep_id INTEGER NOT NULL,
ref_id INTEGER NOT NULL,
CONSTRAINT dep_table_pkey PRIMARY KEY (dep_id)
);
ALTER TABLE dep_table ADD CONSTRAINT dep_table_depends_on_ref_table
FOREIGN KEY (ref_id)
REFERENCES ref_table (ref_id)
MATCH FULL ON DELETE NO ACTION ON UPDATE CASCADE NOT DEFERRABLE;
CREATE OR REPLACE FUNCTION fn_trg_mark_ref_as_deleted() RETURNS TRIGGER AS
$$
BEGIN
UPDATE ref_table SET deleted = TRUE
WHERE ref_id = OLD.ref_id;
RETURN OLD;
END;
$$
LANGUAGE 'plpgsql' VOLATILE;
CREATE TRIGGER mark_ref_as_deleted BEFORE DELETE ON ref_table
FOR EACH ROW EXECUTE PROCEDURE fn_trg_mark_ref_as_deleted();
COMMIT;
----------------------------------------------------------------------------
--------
-- SAMPLE DATA:
BEGIN;
DELETE FROM dep_table;
DELETE FROM ref_table;
INSERT INTO ref_table (ref_id) VALUES (1);
INSERT INTO ref_table (ref_id) VALUES (2);
INSERT INTO ref_table (ref_id) VALUES (3);
INSERT INTO ref_table (ref_id) VALUES (4);
INSERT INTO ref_table (ref_id) VALUES (5);
INSERT INTO dep_table (dep_id,ref_id) VALUES (100,1);
INSERT INTO dep_table (dep_id,ref_id) VALUES (101,1);
INSERT INTO dep_table (dep_id,ref_id) VALUES (102,2);
INSERT INTO dep_table (dep_id,ref_id) VALUES (103,2);
INSERT INTO dep_table (dep_id,ref_id) VALUES (104,3);
COMMIT;
----------------------------------------------------------------------------
--------
-- SAMPLE QUERIES (which don't do what I would like!):
DELETE FROM ref_table WHERE ref_id = 1 -- Ideally should sets the 'deleted'
flag and not remove the row. (works OK)
DELETE FROM ref_table WHERE ref_id = 5 -- Ideally should remove the row
completely. (Does not work OK)
----------------------------------------------------------------------------
--------
Thanks all,
Simon Kinsella
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
