Search Postgresql Archives

Re: Affected # of Rows After TRIGGER/RULE Return

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Wednesday 14 May 2008 19:10:18 Volkan YAZICI wrote:
> On Wed, 14 May 2008, Gerald Quimpo <bopolissimus.lists@xxxxxxxxx> 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@xxxxxxxxx
  What we call Progress is the exchange of one nuisance for anothe
   nuisance.
         -- Havelock Ellis


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux