CODEWITHSUNDEEP
mysql
Pete Watters Chatsworth
Pete Watters Chatsworth

Reputation: 33

Before Insert trigger setting one column = to another column based on IF Statement

On an insert if a column does not have a value entered its value will be null

If its NULL I want to set the value of the column to another column in the insert record

On an Insert trigger on use the code

CREATE DEFINER = CURRENT_USER TRIGGER 
`infrastructure`.`Wall_Drop_BEFORE_INSERT` BEFORE INSERT ON `Wall_Drop` 
FOR EACH ROW
BEGIN

If new.drop_label = null then 
set new.drop_label = new.Drop_id;
end if;

END

The value is not updated, it still has the value null after the update

Upvotes: 0

Views: 55

Answers (1)

Paul Spiegel
Paul Spiegel

Reputation: 31812

A comparison with NULL (like new.drop_label = null) will always be evaluated as NULL. Thus can never be TRUE. You need to use either

If new.drop_label is null

Or the NULL-save opreator

If new.drop_label <=> null

Read more about "Working with NULL Values" in the official documentation.

However - What you want, can also be done in one step using COALESCE():

BEGIN

set new.drop_label = coalesce(new.drop_label, new.Drop_id);

END

Upvotes: 3

Related Questions