mysql数据库添加触发器,当已经存在自增列时,使某个字段数值连续,获取某个字段的最大值加一后更新;
注意事项:当触发为BEFORE时,不能用OLD,当触发为AFTER时,不能用NEW
DROP TRIGGER `TR_INVOICE_HEAD`;
CREATE DEFINER = `root`@`localhost` TRIGGER `TR_INVOICE_HEAD` BEFORE INSERT ON `t_doc_invoice_head` FOR EACH ROW
BEGIN
declare n int;
select CASE WHEN ISNULL(max( LISTING_NUMBER )) THEN 0 else max(LISTING_NUMBER) end into n from t_doc_invoice_head;
SET NEW.LISTING_NUMBER = n+1;
END ;