写个MySql5.1.36触发器,当在AA表中列title插入数据时
判断BB表中列title是否存在相同值
如果不存在,则在BB表中列title也插入数据
如果存在,则在BB表中列num加1
怎么写阿?
判断BB表中列title是否存在相同值
如果不存在,则在BB表中列title也插入数据
如果存在,则在BB表中列num加1
怎么写阿?
解决方案 »
- Mysql 两地数据库同步
- 请教一个MYSQL 自定义函数问题.
- mysql数据库,物品抢购的问题,大家进来看下哈!
- 想同的 sql 在不同机器上执行有的走索引
- 导入大量数据的问题
- 这是什么抱错...
- 在98下安装sql客户端后,新建sql注册,选择系统管理员给我分配的sql登陆信息(sql身份验证),并选择在连接是提示输入sql帐户信息后,注
- 一个表设置了200多个字段会不会太多?
- Mysql如何通过.frm文件 和.ibd文件恢复数据
- 关于sparksql中使用where、group by,having的问题
- mysql表里面有部分内容存成了更gb2312,怎么转成utf8?
- mysql数据库导出导入问题??
for each row
begin
set @x=(select count(*) from BB where BB.title=NEW.title);
if @x>0 then
update BB set num=num+1 where title=NEW.title;
else
insert into BB (num,title) values (1,New.title);
end if;
end;
Query OK, 0 rows affected (0.05 sec)mysql> create table BB (title int primary key,num int);
Query OK, 0 rows affected (0.11 sec)mysql> delimiter //
mysql>
mysql> create trigger tr_AA_bi before insert on AA
-> for each row
-> begin
-> insert into BB (title,num) values (new.title,1)
-> ON DUPLICATE KEY UPDATE num=num+1;
-> end;
-> //
Query OK, 0 rows affected (0.11 sec)mysql> delimiter ;
mysql>mysql> insert into AA values (2,1);
Query OK, 1 row affected (0.06 sec)mysql> insert into AA values (1,1);
Query OK, 1 row affected (0.23 sec)mysql> select * from aa;
+----+-------+
| id | title |
+----+-------+
| 1 | 1 |
+----+-------+
1 row in set (0.00 sec)mysql> select * from bb;
+-------+------+
| title | num |
+-------+------+
| 1 | 1 |
+-------+------+
1 row in set (0.00 sec)mysql> select * from aa;
+----+-------+
| id | title |
+----+-------+
| 1 | 1 |
| 2 | 1 |
+----+-------+
2 rows in set (0.00 sec)mysql> select * from bb;
+-------+------+
| title | num |
+-------+------+
| 1 | 2 |
+-------+------+
1 row in set (0.00 sec)mysql>
delimiter //
create trigger tri_update_bb after insert on aa
for each row
begin
set @n=(select count(*) from bb where bb.title=new.title);
if @n>0 then
update BB set num=num+1 where title=new.title;
else
insert into BB(title,num) values (new.title,1);
end if;
end;
delimiter ;