mysql - 更新一列后将该行移动到 sql 中的另一个表

Question

如何在完成更新功能后将表 1 中的一行移动到表 2。

table1 : mem_id, st_id, name, num, status, date

table2 : mem_id, st_id, name, num, status, date

当我从 table1 更新状态时，该行应该移动到 codeigniter 中的 table2。

Answer 1

创建示例表

create table table1(mem_id INT, st_id INT, name CHAR(30), num INT, status INT, date_en DATE);

再创建一个与上面定义相同的表

create table table2 like table1;

在table1中插入一些示例数据

INSERT INTO table1 VALUES(1,1,'Vinay',1,1,CURDATE());

SELECT * FROM table1;
+--------+-------+-------+------+--------+------------+
| mem_id | st_id | name  | num  | status | date_en    |
+--------+-------+-------+------+--------+------------+
|      1 |     1 | Vinay |    1 |      1 | 2015-03-12 |
+--------+-------+-------+------+--------+------------+
1 row in set (0.00 sec)

在表 1 上创建触发器

DELIMITER //
CREATE TRIGGER table_after_update AFTER UPDATE ON table1 FOR EACH ROW
BEGIN
    INSERT INTO table2 VALUES(old.mem_id,old.st_id,old.name,old.num,old.status,old.date_en);
END // 
DELIMITER ;

检查您的更新查询

UPDATE table1 SET status = 55 WHERE mem_id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

检查表 1 和表 2 中的数据

SELECT * FROM table1;
+--------+-------+-------+------+--------+------------+
| mem_id | st_id | name  | num  | status | date_en    |
+--------+-------+-------+------+--------+------------+
|      1 |     1 | Vinay |    1 |     55 | 2015-03-12 |
+--------+-------+-------+------+--------+------------+
1 row in set (0.00 sec)

table2中插入数据

SELECT * FROM table2;
+--------+-------+-------+------+--------+------------+
| mem_id | st_id | name  | num  | status | date_en    |
+--------+-------+-------+------+--------+------------+
|      1 |     1 | Vinay |    1 |      1 | 2015-03-12 |
+--------+-------+-------+------+--------+------------+
1 row in set (0.00 sec)