有如下表:create table t1(id int, name varchar(20));create table t2(id int, name varchar(20));
我想写一个触发器,就是当t1表的id内容发声改变时,t2表里的内容全部更新为t1表内的。我写了一个这样的:
create trigger tri1 after update on t1
for each row begin
update into t2 values(NEW.id, NEW.name);
end;该怎么改,才能是只当id发声改变时才激活触 发器?
我想写一个触发器,就是当t1表的id内容发声改变时,t2表里的内容全部更新为t1表内的。我写了一个这样的:
create trigger tri1 after update on t1
for each row begin
update into t2 values(NEW.id, NEW.name);
end;该怎么改,才能是只当id发声改变时才激活触 发器?
解决方案 »
- 一条SQL语句经过函数计算后类型转成了blob类型,怎么回事
- 我想把一列数据前全部加0,如何实现啊?
- 谁可以告诉我怎么备份 恢复数据库啊
- 求一个触发器,用来修改提交数据中的ID为表中没有的ID
- 相同数据过滤问题,请假高手
- mysql修改root密码
- 本机连mysql正常,但是远程连就出现下面问题,ERROR 1249: Client does not support authentication ……
- 我希望成为MySQL分栏的版主,为大家作点贡献,请支持我,过来UP吧!!
- mysql中导入文本文件中的数据,其中汉字变成了问号
- mysql.users里面, root用户的 host设为 % ,所有机器都可以连接; 那么,怎么能“只让特定的10多个IP访问、使用mysql呢” ?
- 关于mysql子查询的问题。
- mysql大数据量时select如何提高效率
是新的还是旧的记录?是UPDATE还是INSERT?
新的:
create trigger tri1 after update on t1
for each row begin
INSERT into t2 values(NEW.id, NEW.name);
end;
IF NEW.ID<>OLD.ID THEN
INSERT into t2 values(NEW.id, NEW.name);
END IF
但当INSERT / UPDATE的时候怎么办呢?
mysql> create table t1(id int, name varchar(20));
Query OK, 0 rows affected (0.09 sec)mysql> create table t2(id int, name varchar(20));
Query OK, 0 rows affected (0.05 sec)mysql> delimiter //mysql> create trigger tri1 after update on t1
-> for each row
-> begin
-> if new.id != old.id then
-> update t2 set id=new.id , name=new.name where id=old.id;
-> end if;
-> end//
Query OK, 0 rows affected (0.13 sec)mysql> delimiter ;
mysql> insert into t1 values (1,'a1'),(2,'a2'),(3,'a3');
Query OK, 3 rows affected (0.06 sec)
Records: 3 Duplicates: 0 Warnings: 0mysql> insert into t2 values (1,'a1'),(2,'a2'),(3,'a3');
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | a1 |
| 2 | a2 |
| 3 | a3 |
+------+------+
3 rows in set (0.00 sec)mysql> select * from t2;
+------+------+
| id | name |
+------+------+
| 1 | a1 |
| 2 | a2 |
| 3 | a3 |
+------+------+
3 rows in set (0.00 sec)-- ID 无变化
mysql> update t1 set name=concat(name,11);
Query OK, 3 rows affected (0.02 sec)
Rows matched: 3 Changed: 3 Warnings: 0mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | a111 |
| 2 | a211 |
| 3 | a311 |
+------+------+
3 rows in set (0.00 sec)mysql> select * from t2;
+------+------+
| id | name |
+------+------+
| 1 | a1 |
| 2 | a2 |
| 3 | a3 |
+------+------+
3 rows in set (0.00 sec)-- ID 有变化
mysql> update t1 set id=id+10;
Query OK, 3 rows affected (0.06 sec)
Rows matched: 3 Changed: 3 Warnings: 0mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 11 | a111 |
| 12 | a211 |
| 13 | a311 |
+------+------+
3 rows in set (0.00 sec)mysql> select * from t2;
+------+------+
| id | name |
+------+------+
| 11 | a111 |
| 12 | a211 |
| 13 | a311 |
+------+------+
3 rows in set (0.00 sec)mysql>