有个触发器,大致是这样
DELIMITER $$
CREATE TRIGGER `update_spec` BEFORE UPDATE ON `goods`
FOR EACH ROW
BEGIN
DECLARE aa INT(10) DEFAULT 0;
DECLARE bb INT(10) DEFAULT 0;
IF (NEW.xx <> OLD.xx) OR (NEW.yy <> OLD.yy) THEN
IF NEW.xx <=> 1 THEN
CASE OLD.yy
WHEN 1 THEN SET aa = -(NEW.yy);
WHEN 2 THEN SET bb = -(NEW.yy);
ELSE SET @tmp = 1;
END CASE;
ELSE
CASE NEW.yy
WHEN 1 THEN SET bb = NEW.yy;
WHEN 2 THEN
CASE OLD.yy
WHEN 1 THEN SET aa = NEW.yy;
WHEN 3 THEN SET aa = -(NEW.yy);
END CASE;
ELSE SET @tmp = 1;
END CASE;
END IF;
UPDATE `spec`
SET `spec`.`aa` = `spec`.`aa` + aa, `spec`.`bb` = `spec`.`bb` + bb
WHERE `spec`.`gid` = NEW.`id`;
END IF;
END$$
DELIMITER ;
像上面这样嵌套IF语句和CASE语句,可以吗?
IF NEW.xx <=> 1 THEN
这句语句是否可以可理解为其他编程语言中的
if XXX == 1 ...
这样的语句??
还有,如何调试触发器呢?
DELIMITER $$
CREATE TRIGGER `update_spec` BEFORE UPDATE ON `goods`
FOR EACH ROW
BEGIN
DECLARE aa INT(10) DEFAULT 0;
DECLARE bb INT(10) DEFAULT 0;
IF (NEW.xx <> OLD.xx) OR (NEW.yy <> OLD.yy) THEN
IF NEW.xx <=> 1 THEN
CASE OLD.yy
WHEN 1 THEN SET aa = -(NEW.yy);
WHEN 2 THEN SET bb = -(NEW.yy);
ELSE SET @tmp = 1;
END CASE;
ELSE
CASE NEW.yy
WHEN 1 THEN SET bb = NEW.yy;
WHEN 2 THEN
CASE OLD.yy
WHEN 1 THEN SET aa = NEW.yy;
WHEN 3 THEN SET aa = -(NEW.yy);
END CASE;
ELSE SET @tmp = 1;
END CASE;
END IF;
UPDATE `spec`
SET `spec`.`aa` = `spec`.`aa` + aa, `spec`.`bb` = `spec`.`bb` + bb
WHERE `spec`.`gid` = NEW.`id`;
END IF;
END$$
DELIMITER ;
像上面这样嵌套IF语句和CASE语句,可以吗?
IF NEW.xx <=> 1 THEN
这句语句是否可以可理解为其他编程语言中的
if XXX == 1 ...
这样的语句??
还有,如何调试触发器呢?
IF NEW.xx <=> 1 THEN
<=>
是等值判断??