mysql before insert trigger Error Code 1364: Field does not have a default value and Error Code 1442. Can't update table in stored function/trigger

Question

I have a hierarchical table (left/right values to determine the hierarchy) in my database and I would like to add a "before insert" trigger. One of the problems with large hierarchical tables of this sort is that it can be hard to figure out what has to change when you insert a new item into the hierarchy. So I wanted to automate this in my trigger.

So, my table looks like this...

    name varchar(25) not null,
    leftValue int not null,
    rightValue int not null

Some data...

    'DEFAULT',           1, 10
    'FirstChild',        2, 3
    'SecondChild',       4, 7
    'SecondChildsChild', 5, 6
    'ThirdChild',        8, 9

Now I want to add a 'FirstChildsChild'. This will affect every row except default. I would like the user to only have to do something simple like this...

    SET parent = 3;  -- The left value of FirstChild
    insert into myTable (name, leftValue) VALUES ('FirstChildsChild', parent)

My new table (after properly executing the trigger that I am having trouble with) should look like this...

    'DEFAULT',           1, 12
    'FirstChild',        2, 5
    'FirstChildsChild',  3, 4
    'SecondChild',       6, 9
    'SecondChildsChild', 7, 8
    'ThirdChild',        10, 11

So I wrote the following trigger...

    CREATE TRIGGER `myTrigger` BEFORE INSERT ON `myTable` FOR EACH ROW
    begin
        SET new.rightValue = new.leftValue + 1;

        UPDATE MissionFactors SET
            leftValue = leftValue + 2
        WHERE
            leftValue >= new.leftValue;

        UPDATE MissionFactors SET
            rightValue = rightValue + 2
        WHERE 
            rightValue >= new.leftValue;
    END

FINALLY my questions: 1. Since the trigger is a BEFORE insert, why won't it let me leave the right value undefined in my insert statement? The trigger fills in the value.

    Error Code: 1364. Field 'rightValue' doesn't have a default value.

If I do pass in a right value...

Error Code: 1442. Can't update table 'mytable' in stored function/trigger
because it is already used by statement which invoked this stored function/trigger.

I could write a stored procedure to do all of this. But I thought that this type of thing was one of the reasons why triggers were created.

Bertrand · Accepted Answer · 2014-09-09 08:00:36Z

0

I encountered the same problem.

You have to put the column nullable if you don't want it to appear in your insert's column list even if a trigger sets a default value.

For your second problem, you can't update a table while it's open for inserting. In Oracle there is a similar problem called 'mutating table'. If you want to do this you have to use several triggers including an after statement trigger that update the table.

answered Sep 9, 2014 at 8:00

Bertrand

303 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql before insert trigger Error Code 1364: Field does not have a default value and Error Code 1442. Can't update table in stored function/trigger

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related