I wrote:
> On 10/8/07, chetana bhargav <[EMAIL PROTECTED]> wrote:
> > >> Is this a garbage collection situation, where you want a row in B to go
> > >> away when all referring rows in A are deleted?
> >
> > Yes exactly this is what I wanted :)
> Maintaining a reference count in table B may be more efficient from an
> I/O standpoint, but you'll probably have to do checking within app
> code (instead of using SQL triggers directly), and it would likely
> only pay off if table A has both a vey large number of total rows, as
> well as many references to the same key in table B. It's also not as
> clean from an SQL standpoint, since it's an additional item that must
> be tracked, yet has nothing to do with your actual data dependencies.
After thinking a bit more about implementing this, a pair of triggers
can accomplish it without doing anything special in the app:
CREATE TRIGGER DeRef AFTER DELETE ON A
BEGIN
UPDATE B SET refcount = refcount-1 WHERE key = OLD.refkey;
END;
CREATE TRIGGER DelOrphans AFTER UPDATE ON B
WHEN NEW.refcount = 0
BEGIN
DELETE FROM B WHERE rowid = NEW.rowid;
END;
I still favor the simpler approach, but at least this one is workable.
I also choose lousy trigger names :)
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
