触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句:
- DELETE
- INSERT
- UPDATE
其他MySQL语句不支持触发器,只有表才支持触发器,视图不支持(临时表也不支持)
创建触发器
CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added';
删除触发器
DROP TRIGGER newproduct;
使用触发器
INSERT触发器
CREATE TRIGGER neworder AFTER INSERT ON orders
FOR EACH ROW SELECT NEW.order_num;
-- INSERT INTO orders(order_date, cust_id)
-- VALUES(Now(), 10001);
DELETE触发器
DELIMITER //
CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
INSERT INTO archive_orders(order_num, order_date, cust_id)
VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);
END//
DELIMITER ;
UPDATE触发器
CREATE TRIGGER updatevendor BEGORE UPDATE ON vendors
FOR EACH ROW SET NEW.vend_state = Upper(New.vend_state);