先show databases like 一下,然后根据found_rows()的结果是否为1判断show databases like 'a'; if found_rows()=1 then mysql> show databases like 'a'; Empty set (0.00 sec)mysql> select found_rows(); +--------------+ | found_rows() | +--------------+ | 0 | +--------------+ 1 row in set (0.06 sec)mysql> show databases like 'csdn'; +-----------------+ | Database (csdn) | +-----------------+ | csdn | +-----------------+ 1 row in set (0.00 sec)mysql> select found_rows(); +--------------+ | found_rows() | +--------------+ | 1 | +--------------+ 1 row in set (0.00 sec)mysql>
提示错误: 1415--not allowed to return a result set from a triggermy trigger: CREATE TRIGGER a_insert after insert ON a FOR EACH ROW BEGIN show databases like 'a'; set @a=found_rows(); if @a=1 and new.a>0 then insert into b(a) values(new.a); end if; END;
改成如下create table a (a int); create table b (a int);delimiter // CREATE TRIGGER a_insert after insert ON a FOR EACH ROW BEGIN declare bDBexists int default 0; select count(*) into bDBexists from information_schema.SCHEMATA where SCHEMA_NAME='a'; if bDBexists=1 and new.a>0 then insert into b(a) values(new.a); end if; END; // delimiter ;select * from a; select * from binsert into a values (2); mysql> create table a (a int); Query OK, 0 rows affected (0.09 sec)mysql> create table b (a int); Query OK, 0 rows affected (0.09 sec)mysql> delimiter // mysql> CREATE TRIGGER a_insert after insert ON a -> FOR EACH ROW -> BEGIN -> declare bDBexists int default 0; -> select count(*) into bDBexists from information_schema.SCHEMATA where SCHEMA_NAME='a'; -> if bDBexists=1 and new.a>0 then -> insert into b(a) values(new.a); -> end if; -> END; -> // Query OK, 0 rows affected (0.09 sec)mysql> delimiter ; mysql> mysql> select * from a; Empty set (0.00 sec)mysql> select * from b; Empty set (0.00 sec)mysql> create database a; Query OK, 1 row affected (0.00 sec)mysql> insert into a values (2); Query OK, 1 row affected (0.05 sec)mysql> select * from a; +------+ | a | +------+ | 1 | | 2 | +------+ 2 rows in set (0.00 sec)mysql> select * from b; +------+ | a | +------+ | 2 | +------+ 1 row in set (0.00 sec)mysql>
strong, Admire. I have to study hard after I see your reply.
if found_rows()=1 then
mysql> show databases like 'a';
Empty set (0.00 sec)mysql> select found_rows();
+--------------+
| found_rows() |
+--------------+
| 0 |
+--------------+
1 row in set (0.06 sec)mysql> show databases like 'csdn';
+-----------------+
| Database (csdn) |
+-----------------+
| csdn |
+-----------------+
1 row in set (0.00 sec)mysql> select found_rows();
+--------------+
| found_rows() |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)mysql>
1415--not allowed to return a result set from a triggermy trigger:
CREATE TRIGGER a_insert after insert ON a
FOR EACH ROW
BEGIN
show databases like 'a';
set @a=found_rows();
if @a=1 and new.a>0 then
insert into b(a) values(new.a);
end if;
END;
create table b (a int);delimiter //
CREATE TRIGGER a_insert after insert ON a
FOR EACH ROW
BEGIN
declare bDBexists int default 0;
select count(*) into bDBexists from information_schema.SCHEMATA where SCHEMA_NAME='a';
if bDBexists=1 and new.a>0 then
insert into b(a) values(new.a);
end if;
END;
//
delimiter ;select * from a;
select * from binsert into a values (2);
mysql> create table a (a int);
Query OK, 0 rows affected (0.09 sec)mysql> create table b (a int);
Query OK, 0 rows affected (0.09 sec)mysql> delimiter //
mysql> CREATE TRIGGER a_insert after insert ON a
-> FOR EACH ROW
-> BEGIN
-> declare bDBexists int default 0;
-> select count(*) into bDBexists from information_schema.SCHEMATA where SCHEMA_NAME='a';
-> if bDBexists=1 and new.a>0 then
-> insert into b(a) values(new.a);
-> end if;
-> END;
-> //
Query OK, 0 rows affected (0.09 sec)mysql> delimiter ;
mysql>
mysql> select * from a;
Empty set (0.00 sec)mysql> select * from b;
Empty set (0.00 sec)mysql> create database a;
Query OK, 1 row affected (0.00 sec)mysql> insert into a values (2);
Query OK, 1 row affected (0.05 sec)mysql> select * from a;
+------+
| a |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)mysql> select * from b;
+------+
| a |
+------+
| 2 |
+------+
1 row in set (0.00 sec)mysql>