MySQL触发器带条件判断

ぁ'cultrue

于 2024-09-11 16:32:31 发布

阅读量253

点赞数 2

文章标签： mysql android chrome

本文链接：https://blog.csdn.net/weixin_44487537/article/details/142143650

版权


### 售票数据新增触发器

DELIMITER //

CREATE TRIGGER idcart_message_trigger_insert
AFTER INSERT ON sale_ticket FOR EACH ROW
BEGIN
	DECLARE idCard_is_null VARCHAR(32);
	DECLARE curAge INT(11);
	SET idCard_is_null = new.cert_num;
	-- 只操作身份证信息不为空的
	IF (idCard_is_null IS NOT NULL AND idCard_is_null != '') THEN
		SET curAge = FLOOR(DATEDIFF(CURRENT_DATE(), STR_TO_DATE(SUBSTRING(new.cert_num, 7, 8), '%Y%m%d'))/365);
		INSERT INTO `ticket_expand`(`id`, `ticket_num`, `product`, `sale_num`, `sale_time`, 
		`play_time`, `ver_time`, `sale_man`, 		`sale_channel`, `sale_unit`, `sale_price`, `settle_price`, 
		`person`, `sale_money`, `discount`, `distribution_type`, `vendor`, `status`, `act_money`, `cert_num`, 
		`name`, `book_number`, `create_time`, `update_time`, `source_province`, `province_in`, `province_out`, 
		`sex_man`, `sex_woman`, `ltTwenty`, `thirty`, `forty`, `fifty`, `sixty`, `gtSixty`) 
		VALUES 
		(new.`id`, new.`ticket_num`, new.`product`, new.`sale_num`,new. `sale_time`, 
		new.`play_time`, new.`ver_time`, new.`sale_man`, 		new.`sale_channel`, new.`sale_unit`, 
		new.`sale_price`, new.`settle_price`, new.`person`, new.`sale_money`, new.`discount`, 
		new.`distribution_type`, new.`vendor`, new.`status`, new.`act_money`, new.`cert_num`, 
		new.`name`, new.`book_number`, new.`create_time`, new.`update_time`, 
		LEFT(new.cert_num,2), IF(LEFT(new.cert_num,2) = '41',1,0), IF(LEFT(new.cert_num,2) = '41',0,1), 
		IF(SUBSTR(cert_num, 17, 1) % 2 = 0,0,1), IF(SUBSTR(cert_num, 17, 1) % 2 = 0,1,0), IF( curAge <= 20 ,1,0), 
		IF(21 <= curAge AND curAge <= 30 ,1,0 ), IF(31 <= curAge AND curAge <= 40 ,1,0), IF(41 <= curAge AND curAge <= 50 ,1,0 ), 
		IF(51 <= curAge AND curAge <= 60 ,1,0), IF(curAge >= 61 ,1,0));
	ELSE
		INSERT INTO `zhoukou_sale_ticket_expand`(`id`, `ticket_num`, `product`, `sale_num`, `sale_time`, 
		`play_time`, `ver_time`, `sale_man`, 		`sale_channel`, `sale_unit`, `sale_price`, `settle_price`, 
		`person`, `sale_money`, `discount`, `distribution_type`, `vendor`, `status`, `act_money`, `cert_num`, 
		`name`, `book_number`, `create_time`, `update_time`) 
		VALUES 
		(new.`id`, new.`ticket_num`, new.`product`, new.`sale_num`,new. `sale_time`, 
		new.`play_time`, new.`ver_time`, new.`sale_man`, 		new.`sale_channel`, new.`sale_unit`, 
		new.`sale_price`, new.`settle_price`, new.`person`, new.`sale_money`, new.`discount`, 
		new.`distribution_type`, new.`vendor`, new.`status`, new.`act_money`, new.`cert_num`, 
		new.`name`, new.`book_number`, new.`create_time`, new.`update_time`);
	END IF;
END //

DELIMITER ;