CODEWITHSUNDEEP
mysqltriggers
Jarod
Jarod

Reputation: 495

Setting a value to NULL in ‘BEFORE UPDATE’ mysql trigger

I have the following trigger: 

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 ;

It should keep NULL value in agency_name field whenever the type is INDEPENDENT. And it works as expected only if I set agency_name to anything else but NULL, e.g.g SET NEW.agency_name = 'some value’.

How can I fix this?

Update 22/02/2016:

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, '')

Now if you update the agency_name field, it gets updated. 

UPDATE users SET agency_name = '12312’;

But if you set NEW.agency_name = 'some' in the trigger the value updates to 'some'.

Upvotes: 1

Views: 5925

Answers (1)

Jarod
Jarod

Reputation: 495

Thanks to @wchiquito, 3.3.4.6 Working with NULL Values was really helpful, and !(firstValue <=> secondValue) did the trick.

DROP TRIGGER IF EXISTS user_update_trigger;
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 ;

Upvotes: 2

Related Questions