Re: Affected # of Rows After TRIGGER/RULE Return

On Wednesday 14 May 2008 19:10:18 Volkan YAZICI wrote:
> On Wed, 14 May 2008, Gerald Quimpo <bopolissimus(dot)lists(at)gmail(dot)com> writes:
> > Instead of trying to update the row in place, insert the row again,
> > but with the field you need to mangle (in your example, "plate",
> > in my example below, "k") already mangled. this only works if
> > the field you're mangling is the primary key. if some other field
> > is the primary key, you will need to mangle that too, if possible.
> > since you'll have already inserted the row, just return OLD and let
> > the old row actually be deleted.
>
> I've considered that too. But the problem is that there are nearly 50-60
> tables referencing to the related row about the be deleted. Therefore,
> if I'd return OLD from the trigger, all other rows referencing to OLD
> will get deleted because of ON DELETE CASCADE. But, if there would be
> some way to tell the ON DELETE CASCADE constraints that "Hey, don't move
> yet. I'll INSERT a new row with what you thought to be missing
> previously." there won't be a problem.
>
> I hope I understand you correctly. Did I miss anything? Any ideas?

Not really :-). I was just looking at the simplest possible thing that could
work. I've looked at versioned/temporal databases. But you probably
can't go there since it definitely adds a lot of complexity to your app
and queries. Are you able to change your representation of "deleted"
rows? e.g., can you have an

is_deleted boolean not null default false

column there? that's how i'd have gone, myself, instead of mangling
the plate number.

good luck.

tiger

--
Gerald Timothy Quimpo bopolissimus(at)gmail(dot)com
What we call Progress is the exchange of one nuisance for anothe
nuisance.
-- Havelock Ellis