带声明值的mysql触发器

wfauudbj 于 2021-06-20 发布在 Mysql

关注(0)|答案(1)|浏览(246)

我想使用declare on trigger设置值。触发器如下：

CREATE DEFINER=`root`@`localhost` TRIGGER `update_queue_after_insert` AFTER INSERT ON `encounter_note` FOR EACH ROW DECLARE is_exist INT;
  SET is_exist = ( SELECT count(*) FROM practice_last_updated_module WHERE practice_id = NEW.practice_id );

IF NEW.enc_source = 'OP' THEN
UPDATE practice_queue_list PQL SET
PQL.vital_check = IF (NEW.vs_weight <> 0 OR NEW.vs_height <> 0 OR NEW.  vs_temperature <> 0 OR LENGTH(NEW.vs_blood_pressure) > 0 <> NEW.vs_pulse <> 0 OR NEW.vs_respiration <> 0, 1, 0)
WHERE PQL.encounter_id = NEW.id AND PQL.practice_place_id = NEW.practice_id;
END IF;

IF is_exist > 0 THEN
      UPDATE practice_last_updated_module SET encounter = UNIX_TIMESTAMP(NOW()) where practice_id = NEW.practice_id;
ELSE:
    INSERT INTO practice_last_updated_module (practice_id, encounter) VALUES (NEW.practice_id, UNIX_TIMESTAMP(NOW()));
END IF;"

但它在保存时返回错误
以下查询失败：“create definer= root @ localhost 触发 update_queue_after_insert 插入之后 encounter_note 对于每一行，声明为\u exist int；set is \u exist=（选择count（*）from practice \u last \u updated \u模块，其中
这句话怎么了？

mysql triggers declare

来源：https://stackoverflow.com/questions/51924090/mysql-trigger-with-declare-value

1条答案

按热度按时间

imzjd6km1#

使用13.6.1开始。。。结束复合语句语法。
尝试（可能需要使用分隔符）：

DELIMITER //

CREATE TRIGGER `update_queue_after_insert` AFTER INSERT ON `encounter_note`
FOR EACH ROW
BEGIN -- <- BEGIN
  DECLARE is_exist INT;
  SET is_exist = ( SELECT count(*) FROM practice_last_updated_module WHERE practice_id = NEW.practice_id );

  IF NEW.enc_source = 'OP' THEN
    UPDATE practice_queue_list PQL
    SET PQL.vital_check = IF (NEW.vs_weight <> 0 OR NEW.vs_height <> 0 OR NEW.vs_temperature <> 0 OR LENGTH(NEW.vs_blood_pressure) > 0 <> NEW.vs_pulse <> 0 OR NEW.vs_respiration <> 0, 1, 0)
    WHERE PQL.encounter_id = NEW.id AND PQL.practice_place_id = NEW.practice_id;
  END IF;

  IF is_exist > 0 THEN
    UPDATE practice_last_updated_module SET encounter = UNIX_TIMESTAMP(NOW()) where practice_id = NEW.practice_id;
  -- ELSE:
  ELSE 
    INSERT INTO practice_last_updated_module (practice_id, encounter) VALUES (NEW.practice_id, UNIX_TIMESTAMP(NOW()));
  END IF;
END// -- <- END

DELIMITER ;

赞(0）回复(0）举报 2021-06-20

我来回答

带声明值的mysql触发器

1条答案

相关问题

热门标签

最新问答