Re: Can't delete - Need cascading update instead

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