触发器是因为计算用户忠诚度才来计算的,上次只是生成了一个中间表,在A表中插入数据的同时会在B表中插入对应的数据,设置了对应的计算规则及触发逻辑。
create table aa asSELECT merchant_no,merchant_name from v2.transaction_detail_202011 where DATE_FORMAT(trans_time,'%d')=30;create table bb asSELECT merchant_no,order_no from v2.transaction_detail_202011 where DATE_FORMAT(trans_time,'%d')=30;create table cc asSELECT order_no,trans_amount from v2.transaction_detail_202011 where DATE_FORMAT(trans_time,'%d')=30;create table dd asSELECT order_no,trans_time from v2.transaction_detail_202011 where DATE_FORMAT(trans_time,'%d')=30;
第二步:写触发器
DELIMITER $CREATE trigger aabb_delete on aa for delete as begindelete bb where merchant_no in (select merchant_no from deleted);End$CREATE trigger bbcd_delete on bbfor delete as begindelete cc where order_no in (select order_no from deleted);delete dd where order_no in (select order_no from deleted);End$DELIMITER ;
报错记录:
CREATE trigger aabb_delete on aa for delete as begindelete bb where merchant_no in (select merchant_no from deleted);End> 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'on aa for delete as begin delete bb where merchant_no in (select merchant' at line 2> 时间: 0s
这个问题只能留着以后再来研究了。先mark一下!