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
Message-ID: <20060301142015.3C93319B959@smtp03l.fasthosts.co.uk> (raw)
In-Reply-To: <Pine.LNX.4.44.0603011640220.21298-100000@matrix.gatewaynet.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: simon@bluefiresystems.co.uk
Subject: Re: Help with trigger that updates a row prior to a potentially aborted deletion?
In-Reply-To: <20060301142015.3C93319B959@smtp03l.fasthosts.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox