Re: [QUESTIONS] Re: [HACKERS] text should be a blob field

On Wed, 4 Mar 1998, Jan Wieck wrote:

> Bruce wrote:
>
> > > 1. Is there a call made by the backend to each datatype when a row is
> > > deleted? I can't see one.
> >
> > Well, you could have a RULE that deletes the large object at row
> > deletion time. However, if two rows point to the same large object, the
> > first one deleting it would delete the large object for the other. The
> > only solution to this is to have a separate large object table, and use
> > reference counts so only the last user of the object deletes it.
>
> I think triggers are more appropriate.
>
> On INSERT check that the large object referenced exists.
>
> On UPDATE if large object reference changes, check that new
> large object exists and check if old large object isn't
> referenced any more in which case drop the old large object.
>
> On DELETE check if large object isn't referenced any more ...
>
> Yes - I like triggers :-)

I'm begining to agree with you here.

So far, I've got the trigger to work, so if a row of a table is deleted,
or an oid referencing a BLOB is updated, then the old BLOB is deleted.
This removes the orphaned BLOB problem.

The only problem I have now, is:

How to get a trigger to be automatically created on a table when the
table is created. This would be required, so the end user doesn't have
to do this (normally from within an application).

This would be required, esp. for expanding the text type (or memo, or
whatever).

Any Ideas?

--
Peter T Mount petermount(at)earthling(dot)net or pmount(at)maidast(dot)demon(dot)co(dot)uk
Main Homepage: http://www.demon.co.uk/finder
Work Homepage: http://www.maidstone.gov.uk Work EMail: peter(at)maidstone(dot)gov(dot)uk