Trigger Problem

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 Received on Sun Mar 30 2003 - 14:39:25 CST