CODEWITHSUNDEEP
mysqltriggersnulldefaultdefault-value
Brook Julias
Brook Julias

Reputation: 2105

mysql trigger on a null value being inserted

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;

Upvotes: 1

Views: 4859

Answers (3)

CodeJohnny
CodeJohnny

Reputation: 71

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.

Upvotes: 2

fab
fab

Reputation: 1859

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

Upvotes: 0

Shakti Singh
Shakti Singh

Reputation: 86406

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

Upvotes: 4

Related Questions