有个表test,表test有个触发器 ttest
在ttest里面需要update一个字段。
在触发器中更改本表字段内容该如何做?还是这个操作不适合用触发器来做呢?ERROR 1442 (HY000): Can't update table 'test' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
在ttest里面需要update一个字段。
在触发器中更改本表字段内容该如何做?还是这个操作不适合用触发器来做呢?ERROR 1442 (HY000): Can't update table 'test' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
解决方案 »
- mysql开发书籍 高性能MySQL(第2版)中文版 MySQL必知必会 下载
- 关于MySQL UTF8编码的问题
- 如果批量删除数据库指定随机内容?
- PostgreSQL ado连接问题
- MySQL高手请进:MySQL会由于内存不足 down 机吗?
- php 与mysql联接的问题
- 我为什么不能创建MYSQL用户....
- 请问mysql_fetch_fields()的返回结果需要自己释放吗?
- 急急的问大鸟们到哪里http.ftp上可以下mysql-3.23.19-beta-win.zip?
- 关于超大数据量的数据表设计方式
- MySQL启动日志后,不能启动mysql服务了,请高手帮忙
- 一道数据库面试题(变态)
MYSQL TRIGGER中,不允许对本表进行操作你要达到什么目的,举例说明
如果符合的时候,需要在这个表更新另外一个字段CREATE TABLE `test` (
`id` int(11) unsigned NOT NULL auto_increment,
`ip` bigint(20) default NULL,
`mask` bigint(20) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM;CREATE TABLE `test1` (
`id` int(11) unsigned NOT NULL auto_increment,
`ip` bigint(20) default NULL,
`NewField` varchar(255) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM;CREATE TRIGGER `ok`.`asdf` AFTER INSERT ON `ok`.`test1`
FOR EACH ROW begin
declare lastid int;
declare insertip bigint;
declare inips int;
select last_insert_id() into lastid;
select ip into insertip from test1 where id = lastid;
select count(*) into inips from test where ((ip & mask) = (insertip & mask));
if inips > 0
then
update test1 set NewField = 'ok!';end if;
end;
try:
before insert:
select ip into insertip from test1 where id = newid.;
select count(*) into inips from test where ((ip & mask) = (new.id & mask));
if inips > 0
then
set NewField = 'ok!';end if;
end;
FOR EACH ROW
begin
declare lastid int;
declare insertip bigint;
declare inips int;
select last_insert_id() into lastid;
select ip into insertip from test1 where id = lastid;
select count(*) into inips from test where ((ip & mask) = (insertip & mask));
if inips > 0 then
set New.NewField = 'ok!';
end if;
end;
我原来的意思是:update test1 set NewField = 'ok' where id = lastid;
Updating of NEW row is not allowed in after triggerNEW关键字是不能用在 after trigger的
-> `id` int(11) unsigned NOT NULL auto_increment,
-> `ip` bigint(20) default NULL,
-> `NewField` varchar(255) default NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.50 sec)mysql> delimiter //
mysql> CREATE TRIGGER `asdf` BEFORE INSERT ON `test1`
-> FOR EACH ROW
-> begin
-> declare lastid int;
-> declare insertip bigint;
-> declare inips int;
-> select last_insert_id() into lastid;
-> select ip into insertip from test1 where id = lastid;
-> select count(*) into inips from test where ((ip & mask) = (insertip & mask));
-> if inips > 0 then
-> set New.NewField = 'ok!';
-> end if;
-> end;
-> //
Query OK, 0 rows affected (0.09 sec)mysql> delimiter ;
mysql>
+----+------------+----------+
| id | ip | NewField |
+----+------------+----------+
| 5 | 2043752021 | NULL |
+----+------------+----------+
1 row in set (0.00 sec)mysql> insert into test1 (ip) values(2043752021);
Query OK, 1 row affected, 1 warning (0.02 sec)mysql> select * from test1 order by id desc limit 2;
+----+------------+----------+
| id | ip | NewField |
+----+------------+----------+
| 6 | 2043752021 | NULL |
| 5 | 2043752021 | NULL |
+----+------------+----------+
2 rows in set (0.00 sec)
第六条数据并没有更新NewField的内容阿
int太小了。
现在ok了!谢谢各位!加100分给大家!
-> `id` int(11) unsigned NOT NULL auto_increment,
-> `ip` bigint(20) default NULL,
-> `NewField` varchar(255) default NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.50 sec)mysql> delimiter //
mysql> CREATE TRIGGER `asdf` BEFORE INSERT ON `test1`
-> FOR EACH ROW
-> begin
-> declare lastid int;
-> declare insertip bigint;
-> declare inips bigint;//原来这里是int类型的。数据不能超过2147483647
-> select last_insert_id() into lastid;
-> select ip into insertip from test1 where id = lastid;
-> select count(*) into inips from test where ((ip & mask) = (insertip & mask));
-> if inips > 0 then
-> set New.NewField = 'ok!';
-> end if;
-> end;
-> //
Query OK, 0 rows affected (0.09 sec)mysql> delimiter ;
mysql>触发起是触发了。但是因为数据类型问题,导致select count(*) into inips from test where ((ip & mask) = (insertip & mask));
这条语句为false。