vanseparis
2015-09-19 08:11
采纳率: 0%
浏览 2.2k

mysql创建触发器报错,请高手帮忙看下

DROP TABLE if EXISTS tb;
CREATE TABLE tb(BH CHAR(16),content VARCHAR(20),date DATETIME,val INT);

CREATE TRIGGER tri_NewBH BEFORE INSERT ON tb
FOR EACH ROW
BEGIN
DECLARE dt CHAR(8)
DECLARE bh_id CHAR(16)
DECLARE number INT(1)
DECLARE new_bh VARCHAR(16)

     SET dt = DATE_FORMAT(CURDATE(),'%Y%m%d')

     SELECT
         MAX(BH) INTO bh_id
     FROM tb
     WHERE BH LIKE CONCAT(dt,'%')

     IF bh_id = '' OR bh_id IS NULL THEN
         SET new_bh = CONCAT(dt,'00000001')
     ELSE
        SET number = RIGHT(bh_id,8) + 1
        SET new_bh =  RIGHT(CONCAT('00000000',number),8)
        SET new_bh=CONCAT(dt,new_bh)
     END IF

     SET NEW.BH = new_bh
 END
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • Evankaka 2015-09-19 12:38

    报错信息也放出来看看??

    打赏 评论

相关推荐 更多相似问题