写个MySql5.1.36触发器,当在AA表中列title插入数据时
判断BB表中列title是否存在相同值
如果不存在,则在BB表中列title也插入数据
如果存在,则在BB表中列num加1
怎么写阿?
判断BB表中列title是否存在相同值
如果不存在,则在BB表中列title也插入数据
如果存在,则在BB表中列num加1
怎么写阿?
解决方案 »
- 存储过程 循环问题
- 并集查询太多了. 想简化点. 不知道还有什么好办法
- MySQL字符串
- 现在公司用的最多的数据库是什么数据库??
- 我在linux下安装了mysql后,不能启动,(我这个问题情况特殊,和其他的不能启动的问题好象不同,请斑竹高抬贵手)
- 紧急求助,通过PHP访问MYSQL出现以下问题,请各位大虾帮忙(高分)
- 请问group_concat怎么合并一样的内容
- 使用SQLyog工具连接数据库出现10060报错
- Linux下mysql的数据库存放目录迁移后,mysql无法启动
- 求一个分类的sql语句
- 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 ;