触发器是在插入、更新和删除语句前后自动执行的一堆SQL代码。
DELIMITER $$
CREATE TRIGGER payments_after_insert
AFTER/BEFORE INSERT/UPDATE/DELETE ON payments
FOR EACH ROW
BEGIN
UPDATE invoices
SET payment_total=payment_total+ NEW/OLD.amount
WHERE i.invoice_id=NEW.invoice_id;
END$$
DELIMITER ;
可以通过SHOW TRIGGERS 来显示触发器
通过DROP TRIGGERS删除触发器
事件
DELIMITER $$
CREATE EVENT yearly_delete_stale
ON SCHEDULE
AT '2019-05-01'
--EVERY 1 HOUR STARTS '2019-01-01' ENDS '2020-01-01'
DO BEGIN
DELETE FROM audit
WHERE date< NOW() - INTERVAL 1 YEAR ;
END$$
DELIMITER ;
删除事件:DROP EVENT
修改事件:ALTER EVENT 事件名字 DISABLE/ENABLE