CODEWITHSUNDEEP
mysqltriggers
jlb
jlb

Reputation: 357

mysql trigger : column not part of the update is not null

mysql V 5.6

Hi, I have an issue with a trigger BEFORE UPDATE : on a table, I have a flag defiend as a tinyint, not null, default value 1 . I have a before update trigger that set this flag to 1 whenever the row if updated. But, I also need in one process to explicitly set the flag to 0, so I tried that 

create trigger t BEFORE UPDATE on table for each row
BEGIN
IF new.flag is NULL THEN  
    set new.flag = 1
 END IF;

Problem : new.flag is never null. Checking with 

IF new.flag is NULL THEN  
    set new.flag = 1
ELSEIF new.flag = 0 THEN
    set new.flag = 3
END IF;

set the column to 3 whenever I update the table without including the flag in the update query. Worse, I cannot check against empty string to put the flag to 1, as a select ('' = 0) return true, if using : 

IF new.flag is NULL OR new.flag = '' THEN  
    set new.flag = 1
END IF;

I can never explicitly set the flag to 0 . Shouldn't a column not part of the update be null in NEW ? What can I do, is it a mysql config to change ?

Thanks

Upvotes: 1

Views: 933

Answers (1)

Solarflare
Solarflare

Reputation: 11106

NEW contains the values for all columns that the row will have after the update, not just the values you explicitly used in the update query.

To tell the trigger that you want to explicitly reset the value, you could use an otherwise unused (but valid) value to mark that information, and then act accordingly in the trigger, e.g., in your trigger, use (assuming signed tinyint)

IF new.flag = -1 THEN  
  set new.flag = 0;
ELSE
  set new.flag = 1;
END IF;

Then in your update query, 

update table set flag = -1;

will set the value to 0, any other value, or not using that column in your query at all, will set it to 1.

There is an alternative and more common approach without triggers, that works similar to your flag. You can use a column

last_update datetime default null on update current_timestamp;

null in this column would have the same meaning as flag = 0, any other value would have the same meaning as flag = 1. They are set automatically. If you want to reset your "flag", you can simply use 

update table set last_update = null;

Your trigger approach will of course work too perfectly fine. Just make sure to document that behaviour somewhere.

Upvotes: 2

Related Questions