title: Setting a value to NULL in ‘BEFORE UPDATE’ mysql trigger
body: <p>I have the following trigger: </p>
  
  <pre><code>DELIMITER $$
  CREATE TRIGGER user_update_trigger BEFORE UPDATE ON `users` FOR EACH ROW
  BEGIN
      IF (NEW.type != OLD.type or NEW.agency_name != OLD.agency_name) and NEW.type = 'INDEPENDENT' THEN
          SET NEW.agency_name = NULL;
      END IF;
  END; 
  $$
  DELIMITER ;
  </code></pre>
  
  <p>It should keep <code>NULL</code> value in <code>agency_name</code> field whenever the <code>type</code> is <code>INDEPENDENT</code>. And it works as expected only if I set <code>agency_name</code> to anything else but <code>NULL</code>, e.g.g <code>SET NEW.agency_name = 'some value’</code>.</p>
  
  <p>How can I fix this?</p>
  
  <p><strong>Update 22/02/2016:</strong></p>
  
  <pre><code>CREATE TABLE `users` (
    `id` int(11) NOT NULL,
    `created` datetime NOT NULL,
    `modified` datetime NOT NULL,
    `username` varchar(20) NOT NULL,
    `password` varchar(60) NOT NULL,
    `type` enum('INDEPENDENT','AGENCY') NOT NULL,
    `agency_name` varchar(150) DEFAULT NULL,
    `foto` varchar(100) NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  ALTER TABLE `users` ADD PRIMARY KEY (`id`);
  ALTER TABLE `users` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
  INSERT INTO `users` (`id`, `created`, `modified`, `username`, `password`, `type`, `agency_name`, `foto`) VALUES (NULL, NOW(), NOW(), '1234567', '1234567', 'INDEPENDENT', NULL, '')
  </code></pre>
  
  <p>Now if you update the <code>agency_name</code> field, it gets updated. </p>
  
  <pre><code>UPDATE users SET agency_name = '12312’;
  </code></pre>
  
  <p>But if you set <code>NEW.agency_name = 'some'</code> in the trigger the value updates to 'some'.</p>