From: "Simon Kinsella" <simon@bluefiresystems.co.uk>
To: <pgsql-sql@postgresql.org>
Subject: Re: Help with trigger that updates a row prior to a potentially
	aborted deletion?
Date: Wed, 1 Mar 2006 14:19:43 -0000
Organization: Bluefire Systems Ltd
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: AcY9NYkjEYV3WDYhSGablMHQQE1abQAA3oUQ
In-Reply-To: <Pine.LNX.4.44.0603011640220.21298-100000@matrix.gatewaynet.com>
Message-Id: <20060301142015.3C93319B959@smtp03l.fasthosts.co.uk>

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