Description:
As reported in the Ref. Man. (section: 'The INFORMATION_SCHEMA TRIGGERS Table'), `information_schema`.`TRIGGERS`.`CREATED` is always NULL.
Like `information_schema`.`EVENTS`.`CREATED` for an event, `information_schema`.`TRIGGERS`.`CREATED` should be the date and time when the trigger was created.
Please, update this metadata field on trigger's creation, just like you do for scheduled events. This is a very old issue.
Thanks.
How to repeat:
Test Script:
DROP DATABASE IF EXISTS `trigger_test`;
CREATE DATABASE IF NOT EXISTS `trigger_test`;
USE `trigger_test`;
CREATE TABLE `trigger_test`.`test` (
PRIMARY KEY (`id`),
`id` BIGINT(10) NOT NULL AUTO_INCREMENT ,
`field1` CHAR(30) NULL
)ENGINE = MyISAM
ROW_FORMAT = DEFAULT;
DELIMITER $$
CREATE
TRIGGER `trigger_test`.`test_bu`
BEFORE UPDATE ON `test`
FOR EACH ROW
BEGIN
# DO nothing
END $$
DELIMITER ;
SELECT `CREATED` FROM `information_schema`.`triggers` WHERE `TRIGGER_SCHEMA`='trigger_test' AND `EVENT_OBJECT_TABLE`='test'\G
Script Output:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 5.1.24-rc-community-log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
mysql> DROP DATABASE IF EXISTS `trigger_test`;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE DATABASE IF NOT EXISTS `trigger_test`;
Query OK, 1 row affected (0.00 sec)
mysql> USE `trigger_test`;
Database changed
mysql>
mysql> CREATE TABLE `trigger_test`.`test` (
-> PRIMARY KEY (`id`),
-> `id` BIGINT(10) NOT NULL AUTO_INCREMENT ,
-> `field1` CHAR(30) NULL
-> )ENGINE = MyISAM
-> ROW_FORMAT = DEFAULT;
Query OK, 0 rows affected (0.09 sec)
mysql>
mysql> DELIMITER $$
mysql>
mysql> CREATE
-> TRIGGER `trigger_test`.`test_bu`
-> BEFORE UPDATE ON `test`
-> FOR EACH ROW
-> BEGIN
-> # DO nothing
-> END $$
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> DELIMITER ;
mysql>
mysql> SELECT `CREATED` FROM `information_schema`.`triggers` WHERE `TRIGGER_SCHEMA`='trigger_test' AND `EVENT_OBJECT_TABLE`='test'\G
*************************** 1. row ***************************
CREATED: NULL
1 row in set (0.01 sec)
Suggested fix:
No workaround available. If I am not mistaken there is no way to retrieve the creation datetime for a trigger.
Description: As reported in the Ref. Man. (section: 'The INFORMATION_SCHEMA TRIGGERS Table'), `information_schema`.`TRIGGERS`.`CREATED` is always NULL. Like `information_schema`.`EVENTS`.`CREATED` for an event, `information_schema`.`TRIGGERS`.`CREATED` should be the date and time when the trigger was created. Please, update this metadata field on trigger's creation, just like you do for scheduled events. This is a very old issue. Thanks. How to repeat: Test Script: DROP DATABASE IF EXISTS `trigger_test`; CREATE DATABASE IF NOT EXISTS `trigger_test`; USE `trigger_test`; CREATE TABLE `trigger_test`.`test` ( PRIMARY KEY (`id`), `id` BIGINT(10) NOT NULL AUTO_INCREMENT , `field1` CHAR(30) NULL )ENGINE = MyISAM ROW_FORMAT = DEFAULT; DELIMITER $$ CREATE TRIGGER `trigger_test`.`test_bu` BEFORE UPDATE ON `test` FOR EACH ROW BEGIN # DO nothing END $$ DELIMITER ; SELECT `CREATED` FROM `information_schema`.`triggers` WHERE `TRIGGER_SCHEMA`='trigger_test' AND `EVENT_OBJECT_TABLE`='test'\G Script Output: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 21 Server version: 5.1.24-rc-community-log MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> mysql> DROP DATABASE IF EXISTS `trigger_test`; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> CREATE DATABASE IF NOT EXISTS `trigger_test`; Query OK, 1 row affected (0.00 sec) mysql> USE `trigger_test`; Database changed mysql> mysql> CREATE TABLE `trigger_test`.`test` ( -> PRIMARY KEY (`id`), -> `id` BIGINT(10) NOT NULL AUTO_INCREMENT , -> `field1` CHAR(30) NULL -> )ENGINE = MyISAM -> ROW_FORMAT = DEFAULT; Query OK, 0 rows affected (0.09 sec) mysql> mysql> DELIMITER $$ mysql> mysql> CREATE -> TRIGGER `trigger_test`.`test_bu` -> BEFORE UPDATE ON `test` -> FOR EACH ROW -> BEGIN -> # DO nothing -> END $$ Query OK, 0 rows affected (0.02 sec) mysql> mysql> DELIMITER ; mysql> mysql> SELECT `CREATED` FROM `information_schema`.`triggers` WHERE `TRIGGER_SCHEMA`='trigger_test' AND `EVENT_OBJECT_TABLE`='test'\G *************************** 1. row *************************** CREATED: NULL 1 row in set (0.01 sec) Suggested fix: No workaround available. If I am not mistaken there is no way to retrieve the creation datetime for a trigger.