我有以下触发器。目标是在警报表中插入最接近NOW()14天的日期,而该日期不是周末。目标代码以下面的“ INSERT INTO alarms”开头。我很困惑 谢谢你的帮助。
DELIMITER |
CREATE TRIGGER Invoice_Funnel_AfterUpdate
AFTER UPDATE ON INVOICE
FOR EACH ROW BEGIN
IF (OLD.status != NEW.status && NEW.status in ("1","5","7")
THEN
UPDATE customers
SET set sales_funnel_status = '5', sales_funnel_status_date = NOW()
WHERE customerID = OLD.customerID LIMIT 1
INSERT INTO alarms (username, createdBy, customerID, date_stamp, alert_stamp, minutes_before, active, subject, note,timestamp,funnel,quote,chance)
VALUES (OLD.username,OLD.username,OLD.customerID, (NOW()+ INTERVAL 14 DAY),(NOW()+ INTERVAL 14 DAY),'15','1','Post-Sales Follow-Up Alarm','Contact this customer for referrals and satisfaction survey',
NOW(),'0','0.00',NULL)
END;
|
DELIMITER ;
您可以在now()函数上使用WEEKDAY()或DAYOFWEEK(),这将返回给您发生事件的星期几。
如果一天是周末,则可以添加所需的天数。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句