Re: Trigger Problem

DA Morgan <damorgan_at_exxesolutions.com> wrote in message news:<3E876504.828F421D_at_exxesolutions.com>...
> Scott Krull wrote:

> > CREATE TABLE TKeyword
> > (
> > Pkey INT PRIMARY KEY,
> > KeywordText VARCHAR2(50)
> > );
> >
> > CREATE TABLE TKeywordCaseLink
> > (
> > Pkey INT PRIMARY KEY,
> > KeywordFkey INT CONSTRAINT KeyCaseLinkKeyConstraint REFERENCES
> > TKeyword(Pkey) ON DELETE CASCADE,
> > CaseFkey INT CONSTRAINT KeywordCaseLinkCaseConstraint REFERENCES
> > TCase(Pkey) ON DELETE CASCADE
> > );
> >
> > The trigger in question is:
> > CREATE TRIGGER TRKeywordCaseLinkDelete
> > AFTER DELETE
> > ON TKeywordCaseLink
> > FOR EACH ROW
> > BEGIN
> > DELETE FROM TKeyword WHERE Pkey = :OLD.KeywordFkey;
> > END;
> >

> It appears that you have created a circular reference ... is there a
> delete trigger on TKeyword that in-turn tries to delete from
> TKeywordCaseLink?
>
> Daniel Morgan

The circular reference is that the foreign key from TKeywordCaseLink to TKeyword has ON DELETE CASCADE, so when the user tries to delete from TKeywordCaseLink the trigger causes a delete on TKeyword, and then the foreign key tries to cascade the delete back down to TKeywordCaseLink, ... etc.

The logic seems to be flawed:

> > The purpose of the trigger is to delete orphans of TKeywordCaseLink;
> > that is, whenever a link between a case and a keyword is created, the
> > keyword should also be deleted.

Since TKeyword is a parent of TKeywordCaseLink it would not be "orphaned" by the deletion of a TKeywordCaseLink record. It could become childless, but presumably not always - it appears that the same keyword can be linked to more than one case (else why the intersection table?). This trigger will try to delete the keyword and (via cascade) ALL usages of that keyword for ANY cases - is that really what you intend? More likely you only want to do that when the LAST usage of the keyword is deleted? Even then, you will still hit this circular reference problem.

Question: do you really need TKeyword at all? If you stored the actual Keyword Text in TKeywordCaseLink instead of a pointer to it, the problem would go away:

CREATE TABLE TKeywordCaseLink
(
CaseFkey INT CONSTRAINT KeywordCaseLinkCaseConstraint REFERENCES TCase(Pkey) ON DELETE CASCADE,
KeywordText VARCHAR2(50) NOT NULL,
PRIMARY KEY (CaseFkey,KeywordText)
);

You'll notice I also took the liberty of dropping your Pkey column in TKeywordCaseLink, since a perfectly good natural key exists - unless there are other tables with foreign keys to TKeywordCaseLink that benefit from a surrogate key, which seems unlikely. Received on Mon Mar 31 2003 - 06:01:30 CST