mysql触发器实践

触发器(trigger)：监视某种情况，并触发某种操作。

触发器创建语法四要素：
1.监视地点(table)
2.监视事件(insert/update/delete)
3.触发时间(after/before)
4.触发事件(insert/update/delete)

语法：
create trigger triggerName
after/before insert/update/delete on 表名
for each row #这句话在mysql是固定的
begin
sql语句;
end;

举例说明：
创建两个表

DROP TABLE IF EXISTS `t_a`;CREATE TABLE `t_a` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;DROP TABLE IF EXISTS `t_b`;CREATE TABLE `t_b` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

创建两个触发器：
(1)在t_a插入数据后，往t_b同步数据

DROP TRIGGERIF EXISTS t_afterinsert_on_ta;CREATE TRIGGER t_afterinsert_on_ta AFTER INSERT ON t_a FOR EACH ROWBEGIN INSERT INTO t_b (name)VALUES (new.name);END;

(1)在t_a删除数据后，删除t_b的对应数据​

DROP TRIGGERIF EXISTS t_afterdelete_on_ta;CREATE TRIGGER t_afterdelete_on_ta AFTER DELETE ON t_a FOR EACH ROWBEGIN  delete from t_b where id = old.id;END;​

insert into t_a(name) value(‘liuyanliang’);

SELECT * from t_a
SELECT * from t_b

//发现t_a和t_b插入了一条相同的数据

DELETE FROM t_a where id = 1;

SELECT * from t_a
SELECT * from t_b
​
//发现t_a和t_b的数据都删除了
​