Mysql触发器 有两张表,表A和表B,两张表的表结构一样,B表是A表的历史记录表,逻辑是这样的:当A表中要删除一条记录时,将这条记录插入B表,我不知道用触发器能不能实现?哪位大哥给指点下,谢谢!如果有条件的话,给个test,谢谢! 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 create trigger tr_a_ad after delete on A INSERT INTO B values (old.fld1,old.fld2); CREATE TRIGGER b_insert AFTER DELETE ON AFOR EACH ROWBEGININSERT INTO B(id,name) VALUES (OLD.id,OLD.name);END; CREATE TRIGGER b_insert AFTER DELETE ON AFOR EACH ROWBEGININSERT INTO B(id,`name`) VALUES (OLD.id,OLD.`name`);END; mysql> CREATE TABLE test1(a1 INT,a2 INT);Query OK, 0 rows affected (0.08 sec)mysql> CREATE TABLE test2(a1 INT,a2 INT);Query OK, 0 rows affected (0.06 sec)mysql>mysql> CREATE TRIGGER testref AFTER DELETE ON test1 -> FOR EACH ROW -> INSERT INTO test2 VALUES (old.a1,old.a2);Query OK, 0 rows affected (0.08 sec)mysql>mysql> insert into test1 values (1,1),(2,2),(3,3);Query OK, 3 rows affected (0.06 sec)Records: 3 Duplicates: 0 Warnings: 0mysql> select * from test1;+------+------+| a1 | a2 |+------+------+| 1 | 1 || 2 | 2 || 3 | 3 |+------+------+3 rows in set (0.00 sec)mysql> select * from test2;Empty set (0.00 sec)mysql> delete from test1 where a1<=2;Query OK, 2 rows affected (0.30 sec)mysql> select * from test1;+------+------+| a1 | a2 |+------+------+| 3 | 3 |+------+------+1 row in set (0.00 sec)mysql> select * from test2;+------+------+| a1 | a2 |+------+------+| 1 | 1 || 2 | 2 |+------+------+2 rows in set (0.00 sec)mysql> mysql 获得表记录总数的效率问题 急急急 求助, 有关MySQL协议包 insert语句的询问! 索引是以什么规则建立的????? 关于MySQL中ALTER语句ADD子句中的FIRST 问个问题 用myisampack工具压缩表的时候,总是报错。 shapefile文件导入postgresql数据库,大家帮帮我吧! 如何执行.sql来生成表 关于discuz论坛二次开发操作数据库的问题。 php连接Mysql时,提示内存不能读 mysql的汉字编码问题
INSERT INTO B values (old.fld1,old.fld2);
CREATE TRIGGER b_insert AFTER DELETE ON A
FOR EACH ROW
BEGIN
INSERT INTO B(id,name) VALUES (OLD.id,OLD.name);
END;
FOR EACH ROW
BEGIN
INSERT INTO B(id,`name`) VALUES (OLD.id,OLD.`name`);
END;
Query OK, 0 rows affected (0.08 sec)mysql> CREATE TABLE test2(a1 INT,a2 INT);
Query OK, 0 rows affected (0.06 sec)mysql>
mysql> CREATE TRIGGER testref AFTER DELETE ON test1
-> FOR EACH ROW
-> INSERT INTO test2 VALUES (old.a1,old.a2);
Query OK, 0 rows affected (0.08 sec)mysql>
mysql> insert into test1 values (1,1),(2,2),(3,3);
Query OK, 3 rows affected (0.06 sec)
Records: 3 Duplicates: 0 Warnings: 0mysql> select * from test1;
+------+------+
| a1 | a2 |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+------+------+
3 rows in set (0.00 sec)mysql> select * from test2;
Empty set (0.00 sec)mysql> delete from test1 where a1<=2;
Query OK, 2 rows affected (0.30 sec)mysql> select * from test1;
+------+------+
| a1 | a2 |
+------+------+
| 3 | 3 |
+------+------+
1 row in set (0.00 sec)mysql> select * from test2;
+------+------+
| a1 | a2 |
+------+------+
| 1 | 1 |
| 2 | 2 |
+------+------+
2 rows in set (0.00 sec)mysql>