CODEWITHSUNDEEP
databaseoracle-databasetriggers
user6898323
user6898323

Reputation: 23

Create Trigger on Parent table that creates row in Child table and assigns new Parent PK to Child as FK

I have two tables:

Customer (customerID, firstName, lastName,....)

Account (accountID, currentBalance....customerID) customerID references CUSTOMER.

The tables have a 1:M (Customer:Account), Mandatory-Mandatory relationship. I am trying to setup a trigger that automatically creates a child row when a parent is inserted, after researching Stack and elsewhere I have managed to create a trigger on the parent that creates a row in the child:

CREATE OR REPLACE TRIGGER CMustHaveAccount
     AFTER INSERT ON CUSTOMER
    FOR EACH ROW
    BEGIN
        INSERT INTO ACCOUNT (accountID)
        SELECT SEQACCOUNTID.NEXTVAL
        FROM dual;
    END;
/

All my attempts to set the FK in Account as the new PK in Customer are failing, I tried a number of triggers, the most promising being:

CREATE OR REPLACE TRIGGER AMustHaveCustomer
     AFTER INSERT ON CUSTOMER
    FOR EACH ROW
    BEGIN
        INSERT INTO ACCOUNT (customerID)
        SELECT :new.customerID
        FROM CUSTOMER;
    END;
/

This trigger throws back the error

ORA-04091: table .CUSTOMER is mutating, trigger/function may not see it

. If I change the trigger to BEFORE, it gives error ORA-01400: cannot insert NULL into ("ACCOUNT"."ACCOUNTID"). I am assuming because technically the insert has not been completed so the PK I am inserting into Customer does not yet exist.

I want to have a trigger(s) that inserts a value into Account, with a primary key from my sequence, when a row is created in Customer, and for the PK customerID to automatically be assigned to customerID in ACCOUNT as the foreign key.

I am just learning SQL and Databases, please excuse me if the answer is obvious. Help greatly appreciated!

Upvotes: 2

Views: 1647

Answers (1)

APC
APC

Reputation: 146239

The foreign key of ACCOUNT is the primary key of CUSTOMER, so this should work for you. Note the :new keyword, which is how to reference values in the current record and so avoid the "mutating table" error. 

CREATE OR REPLACE TRIGGER CMustHaveAccount 
AFTER INSERT ON CUSTOMER 
FOR EACH ROW 
BEGIN 
    INSERT INTO ACCOUNT (accountID, currentBalance, customerID) 
    values ( SEQACCOUNTID.NEXTVAL, 0, :NEW.customerID); 
END;
/

Upvotes: 2

Related Questions