Description:
The information schema triggers table does not show information about a trigger before it
is executed if set to the information schema database as described below:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8 to server version: 5.0.11-beta-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> drop database if exists tb1;
Query OK, 1 row affected (0.00 sec)
mysql> create database tb1;
Query OK, 1 row affected (0.01 sec)
mysql> use tb1;
Database changed
mysql> drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create table t1 (f1 integer);
Query OK, 0 rows affected (0.01 sec)
mysql> create trigger tr1 after insert on t1 for each row set @test_var=42;
Query OK, 0 rows affected (0.02 sec)
mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select trigger_schema, trigger_name from triggers where trigger_name='tr1';
Empty set, 1 warning (0.04 sec)
OBN> Note the above should have showen the trigger.
mysql> use tb1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> set @test_var=1;
Query OK, 0 rows affected (0.00 sec)
mysql> select @test_var;
+-----------+
| @test_var |
+-----------+
| 1 |
+-----------+
1 row in set (0.00 sec)
mysql> insert into t1 values (10);
Query OK, 1 row affected (0.00 sec)
mysql> select @test_var;
+-----------+
| @test_var |
+-----------+
| 42 |
+-----------+
1 row in set (0.00 sec)
mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select trigger_schema, trigger_name from triggers where trigger_name='tr1';
+----------------+--------------+
| trigger_schema | trigger_name |
+----------------+--------------+
| tb1 | tr1 |
+----------------+--------------+
1 row in set (0.01 sec)
mysql>
Note: The above in not observed if quering theinformation schema without setting to the
database i.e. using:
use tb1;
select trigger_schema, trigger_name
from information_schema.triggers where trigger_name='tr1';
How to repeat:
drop database if exists tb1;
create database tb1;
use tb1;
drop table if exists t1;
create table t1 (f1 integer);
create trigger tr1 after insert on t1 for each row set @test_var=42;
use information_schema;
select trigger_schema, trigger_name from triggers where trigger_name='tr1';
use tb1;
set @test_var=1;
select @test_var;
insert into t1 values (10);
select @test_var;
use information_schema;
select trigger_schema, trigger_name from triggers where trigger_name='tr1';
Suggested fix:
Information schema should show triggers as soon as they are created
