Re: Trigger Problem

Scott Krull wrote:

> Hello,
>
> I am migrating from a SQL Server 2000 database to an Oracle 8.1
> database, and I'm having trouble with a certain type of trigger. The
> trigger applies to the following tables:
>
> CREATE TABLE TCase
> (
> Pkey INT PRIMARY KEY,
> Title VARCHAR2(200),
> DescriptionText VARCHAR2(2500),
> AddressFkey INT CONSTRAINT CaseAddressConstraint REFERENCES
> TAddress(Pkey),
> CreatedTime DATE,
> CreatedByUserFkey INT CONSTRAINT CaseCreatedByUserConstraint
> REFERENCES TUser(Pkey),
> LeadDepartmentFkey INT CONSTRAINT CaseLeadDepartmentConstraint
> REFERENCES TDepartment(Pkey),
> DueTime DATE,
> ClosedTime DATE,
> StatusFkey INT CONSTRAINT CaseStatusConstraint REFERENCES
> TCaseStatus(Pkey),
> PriorityFkey INT CONSTRAINT CasePriorityConstraint REFERENCES
> TCasePriority(Pkey)
> );
>
> 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;
>
> 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. This works fine in SQL Server, but
> causes problems in Oracle. When I try the command:
>
> delete from tkeywordcaselink where pkey = 1;
> or the command
> delete from tkeywordcaselink;
>
> I get the following:
>
> ERROR at line 1:
> ORA-04091: table SYSTEM.TKEYWORDCASELINK is mutating, trigger/function
> may not
> see it
> ORA-06512: at "SYSTEM.TRKEYWORDCASELINKDELETE", line 2
> ORA-04088: error during execution of trigger
> 'SYSTEM.TRKEYWORDCASELINKDELETE'
>
> What exactly is going wrong with my trigger, and how do I correct it?
>
> Thanks much,
> Scott

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 Received on Sun Mar 30 2003 - 15:43:33 CST