December 10th, 2012, 01:06 PM
Help with a trigger.
Here are the relevant tables to the triggers I'm having trouble with.
create table profile
constraint profile_pk primary key(userID),
constraint profile_ak unique(email)
create table groupMembership
constraint groupMembership_pk primary key(gID, userID),
constraint groupMembership_fk1 foreign key(gID) references groups(gID) on delete cascade,
constraint groupMembership_fk2 foreign key(userID) references profile(userID) on delete cascade
Here's the triggers I currently have that doesn't work.
create or replace trigger DropAccount
after delete on profile
FOR EACH ROW
delete from groupMembership where (userID = :old.userID);
The DropAccount trigger's purpose is to delete all the records from groupMembership that contains the userID of the person deleting his/her own account. It appears to work when I run the following statements in sqlplus:
delete from profile where (userID = '1');
select * from groupMembership where (userID = '1');
But when I try to delete an account through a java interface using JDBC I get the following error:
Machine Error: java.sql.SQLException: ORA-04091: table *****.GROUPMEMBERSHIP is mutating, trigger/function may not see it
ORA-06512: at "*****.DROPACCOUNT", line 2
ORA-04088: error during execution of trigger '*****.DROPACCOUNT'
The java function basically just executes a delete statement.
December 10th, 2012, 03:41 PM
You do not need the trigger because of the DELETE CASCADE in the following:
Originally Posted by Kamonok
CONSTRAINT groupmembership_fk2 FOREIGN KEY(userid)
REFERENCES PROFILE(userid) ON DELETE CASCADE