Re: Help with trigger that updates a row prior to a potentially aborted deletion? - Mailing list pgsql-sql

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_tableFOREIGN KEY (ref_id)REFERENCES ref_table
(ref_id)MATCHFULL 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_tableFOR 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