Re: Can't delete - Need cascading update instead - Mailing list pgsql-general
From | Richard Huxton |
---|---|
Subject | Re: Can't delete - Need cascading update instead |
Date | |
Msg-id | 422EB291.8090604@archonet.com Whole thread Raw |
In response to | Can't delete - Need cascading update instead (Adam Tomjack <adam@zuerchertech.com>) |
List | pgsql-general |
Adam Tomjack wrote: > For various reasons, I can't actually delete records from my database. > Instead, I have a boolean 'active' field for each table. I need to > implement something like cascading delete, but instead of deleting, I > need to set active=false. > > I've googled and haven't found a solution. I had two ideas, neither of > which worked out. > > One thing I tried is to set ON DELETE CASCADE for all of my foreign key > constraints. Then I added a rule ON DELETE DO ALSO UPDATE ... and a > BEFORE DELETE trigger to stop the actual deletion. Unfortunately, that > also stops the cascade. I'd be tempted to add triggers to the delete to copy old versions of the data to a set of archive tables. Alternatively, if you made "active" part of the primary and foreign-keys on the tables concerned you could cascade updates. > My other idea involved an ON DELETE DO INSTEAD UPDATE ... rule and a > BEFORE UPDATE PL/pgSQL trigger that manually implemented the cascading. > The problem with that is that the only way I can find to generate an > approproate UPDATE or DELETE statement is to create a string and then > EXECUTE it, but I need values from the NEW or OLD records, which > apparently aren't usable from an EXECUTE statement. I'll include my > code at the end. > sql := sql || ' ' || r2.attname || '=OLD.' || r3.attname; You can't refer to the OLD.xxx or NEW.xxx in the query-string itself, you need to add its value. Of course, that causes problems because you can't dynamically refer to OLD[r3.attname] or whatever syntax you'd be tempted by. TCL or one of the other dynamic languages is better for this. I've attached a sample of some code and history tables that do something similar to what you're trying to do. I don't make any great claims for my TCL coding skills - most of it was pieced together from tutorials. HTH -- Richard Huxton Archonet Ltd -- History Tracking Trigger-Functions -- CREATE TABLE history ( hid SERIAL UNIQUE NOT NULL, cid int4 NOT NULL CONSTRAINT valid_client REFERENCES client ON DELETE CASCADE, ts timestamp(0) with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, uid int4 NOT NULL CONSTRAINT valid_uid REFERENCES user_info DEFAULT app_session_int_vol('UID'), tbl varchar(32) NOT NULL, act char(1) NOT NULL CONSTRAINT valid_act CHECK (act IN ('U','I','D')), PRIMARY KEY (hid) ); CREATE TABLE history_detail ( hid integer NOT NULL CONSTRAINT valid_hid REFERENCES client_history, col varchar(32) NOT NULL, was text, PRIMARY KEY (hid,col) ); -- tcl_track_history(TABLE-NAME) -- Set TABLE-NAME when creating the trigger. Will automatically record change -- details in tables history/history_detail -- CREATE OR REPLACE FUNCTION tcl_track_history() RETURNS trigger AS ' switch $TG_op { DELETE { if { [llength [array names OLD cid]] > 0 } { set clival $OLD(cid) } else { set clival "NULL" } spi_exec "INSERT INTO history (cid,tbl,act) VALUES ($clival, \'$1\', \'D\')" } INSERT { if { [llength [array names NEW cid]] > 0 } { set clival $NEW(cid) } else { set clival "NULL" } spi_exec "INSERT INTO history (cid,tbl,act) VALUES ($clival, \'$1\', \'I\')" } UPDATE { if { [llength [array names OLD cid]] > 0 } { set clival $OLD(cid) } else { set clival "NULL" } set inserted_main_history_row false foreach {col} $TG_relatts { # First result seems to be an empty string when stepping through columns if { $col > "" } { # Check if OLD/NEW contain a value if { [llength [array names OLD $col]] > 0 } { set oldval $OLD($col) } else { set oldval "NULL" } if { [llength [array names NEW $col]] > 0 } { set newval $NEW($col) } else { set newval "NULL" } if { $oldval != $newval } { if { !$inserted_main_history_row } { spi_exec "INSERT INTO history (cid,tbl,act) VALUES ($clival, \'$1\', \'U\')" set inserted_main_history_row true } spi_exec "INSERT INTO history_detail (col,was) VALUES (\'$col\', \'[ quote $oldval ]\')" } } } } } return OK ' LANGUAGE pltcl; CREATE TRIGGER history_trig AFTER INSERT OR UPDATE OR DELETE ON client FOR EACH ROW EXECUTE PROCEDURE tcl_track_history('client');
pgsql-general by date: