CODEWITHSUNDEEP
sqloracle-databaseplsqldatabase-trigger
user3423975
user3423975

Reputation: 13

Can INSERT when UPDATING but not when INSERTING

            create or replace trigger UPDATE_HISTORY
            BEFORE INSERT OR UPDATE ON MAIN_TABLE
            FOR EACH ROW
            BEGIN

            IF UPDATING THEN

            INSERT INTO HISTORY(
                ID,
                STATUS_ID
            )
            VALUES 
            (
                :OLD.ID,
                :OLD.STATUS_ID
            );

            ELSE

            :NEW.id := id_seq.nextval; 

            INSERT INTO HISTORY(
                ID,
                STATUS_ID
            )
            VALUES 
            (
                :NEW.ID,
                :NEW.STATUS_ID
            );

            END IF;

            END;

I am currently experiencing a problem where I am able to insert data when creating a new record in MAIN_TABLE but when I come to update a record within this table it throws back and error. I am using a sequence to create a new id for the History table.

The error I'm receiving is "violated - parent key not found" / " line 21 ORA-04088: error during execution of trigger".

Upvotes: 1

Views: 73

Answers (2)

Hawk
Hawk

Reputation: 5170

So according to your comment ID is the PK of MAIN_TABLE and the FK of HISTORY: And according to the referrential integrity rule:

A foreign key is a way to enforce referential integrity within your Oracle database. A foreign key means that values in one table must also appear in another table.

The referenced table is called the parent table while the table with the foreign key is called the child table. The foreign key in the child table will generally reference a primary key in the parent table.

On INSERT: Your trigger will insert the new ID (that is inserted into the MAIN_TABLE),into History table. So here, the FK (which is ID in HISTORY) has PK in MAIN_TABLE - works fine.

On UPDATE: , however, the ID in your MAIN_TABLE has already been replaced with new ID. But you are trying to insert the old one into History table (which is already replaced in the MAIN_TABLE). So this FK does not appear in MAIN_TABLE anymore. This violates your referential integrity rule you have it in place.

EXAMPLE
You have a row with ID=3 in MAIN_TABLE, and you update it to ID=5. On update, your trigger is trying to insert ID=3 into History (:old.ID). But 3 does not exist anymore in MAIN_TABLE

SOLUTION
It all depends on the logic you want to implement, and what are you trying to achieve. If you want to store the :old.ID, you might need to remove FK constraint on this particular column. or you probably want to store :old.ID in anther column and use :new.ID as FK

Read about FK here

Upvotes: 0

Dave Costa
Dave Costa

Reputation: 48111

There are two situations here which I think will cause an error.

The one that I think you are hitting is on inserting a new row. You are trying to insert into the history table before the insert into the main table actually occurs. Therefore, the foreign key constraint is violated because there is no parent record for the child to reference. To avoid this, you could set the constraint to be deferred, so it is not checked until you commit; or use an AFTER trigger to do the insert into the history table.

The other problem would be if an update changed the ID of the row in the main table. The trigger would insert a history record; then the update itself would fail because a child record existed with the old ID value. This is probably the desired behavior, as you generally don't want people modifying PK values anyway.

Upvotes: 3

Related Questions