mysql trigger on a null value being inserted

Question

I was wanting a mysql trigger for a specific field. When a NULL value is inserted into this field I would like to replace it with a default value. I know this code is not correct, but it would give you an idea of what I want.

CREATE TABLE IF NOT EXISTS example(
    id  INT NOT NULL    AUTO_INCREMENT,
    parent_id   INT NOT NULL    
);

CREATE TRIGGER insert_parentId_trigger BEFORE INSERT ON example
    FOR EACH ROW BEGIN
        IF parent_id = NULL THEN
           SET parent_id = 0;
        END IF
    END;

why don't you put 0 as default value?

Nicola Cossu
– Nicola Cossu

2011-04-28 16:37:56 +00:00
Commented Apr 28, 2011 at 16:37 — Nicola Cossu
– Nicola Cossu, Commented Apr 28, 2011 at 16:37

Shakti Singh · Accepted Answer · 2011-04-28 16:39:36Z

4

Declare that column with not null default 0 instead of using trigger later.

When mysql is already handling that condition why we need to use trigger here?

You can use alter command to update your definition of your column

ALTER TABLE example MODIFY parent_id INT(11) NOT NULL DEFAULT 0

answered Apr 28, 2011 at 16:39

Shakti Singh

86.7k21 gold badges142 silver badges156 bronze badges

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

Comments

CodeJohnny · Accepted Answer · 2013-04-24 00:07:23Z

2

I agree with the example of a Null be simplistically set to 0, then using a "Default" is fine, but what if you're after a value that's variable and cannot be a Default? For example:

IF `Creator` Is NULL THEN
   SET `Creator` = current_user();
END IF

For this type of use case, or something that requires a lookup, you will not be able to use Default.

answered Apr 24, 2013 at 0:07

CodeJohnny

712 bronze badges

Comments

fab · Accepted Answer · 2011-04-28 16:44:45Z

0

Besides that Shakti Singh is totally right with the default value, you have to compare NULL values with IS instead of =
It should be IF parent_id IS NULL

answered Apr 28, 2011 at 16:44

fab

1,86916 silver badges21 bronze badges

Collectives™ on Stack Overflow

mysql trigger on a null value being inserted

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related