#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    5
    Rep Power
    0

    Help with a trigger.


    Here are the relevant tables to the triggers I'm having trouble with.

    create table profile
    (
    userID number(10),
    uname varchar2(64),
    email varchar2(32),
    password varchar2(32),
    date_of_birth date,
    picture_URL varchar2(128),
    aboutme varchar2(1024),
    lastlogin date,
    constraint profile_pk primary key(userID),
    constraint profile_ak unique(email)
    );

    create table groupMembership
    (
    gID number(10),
    userID number(10),
    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
    BEGIN
    delete from groupMembership where (userID = :old.userID);
    END;
    /

    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.
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    857
    Rep Power
    388

    Cool


    Originally Posted by Kamonok
    . . . Etc . . .
    The java function basically just executes a delete statement.
    You do not need the trigger because of the DELETE CASCADE in the following:
    Code:
    CONSTRAINT groupmembership_fk2 FOREIGN KEY(userid)
        REFERENCES PROFILE(userid) ON DELETE CASCADE
    DUH!

IMN logo majestic logo threadwatch logo seochat tools logo