急!!!MySQL触发器求解!
发布网友
发布时间：2022-04-15 04:59
共1个回答
热心网友
时间：2022-04-15 06:28
同步插入触发器
DELIMITER |
CREATE TRIGGER tg_sync_insert
AFTER INSERT
ON mother
FOR EACH ROW
BEGIN
INSERT INTO son SELECT * FROM mother WHERE id > LAST_INSERT_ID();
END;
|
DELIMITER ;
LAST_INSERT_ID()获取到最后插入表后的id值，所以mother表必须要有一个auto_increment的字段，以为存在同时插入多个记录的情况，需要用大于LAST_INSERT_ID()
同步删除触发器
DELIMITER |
CREATE TRIGGER tg_sync_delete
AFTER DELETE
ON mother
FOR EACH ROW
BEGIN
DELETE FROM son WHERE son.id=OLD.id;
END;
|
DELIMITER ;
同步更新触发器
DELIMITER |
CREATE TRIGGER tg_sync_update
AFTER UPDATE
ON mother
FOR EACH ROW
BEGIN
DELETE FROM son WHERE son.id=OLD.id;
INSERT INTO son SELECT * FROM mother WHERE id = OLD.id;
END;
|
DELIMITER
