我有以下触发器:
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 ;
只要 type
是 INDEPENDENT
,它应该在 agency_name
字段中保留 NULL
值 . 只有当我将 agency_name
设置为除 NULL
之外的任何其他内容时,它才能正常工作,例如 SET NEW.agency_name = 'some value’
.
我怎样才能解决这个问题?
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, '')
现在,如果您更新 agency_name
字段,它将更新 .
UPDATE users SET agency_name = '12312’;
但是如果在触发器中设置 NEW.agency_name = 'some'
,则值将更新为'some' .
1 回答
感谢@wchiquito,3.3.4.6 Working with NULL Values真的很有帮助,
!(firstValue <=> secondValue)
做了伎俩 .