Re: Accessing auto-increment value in Trigger

[William Chiquito]

Hi Aijaz,

I do not know any bug related to this.

My script of test:

CREATE TABLE `t1` (
  `id` int(11) NOT NULL auto_increment,
  `fecha` datetime default NULL,
  `valor` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;

DELIMITER $$

DROP TRIGGER `mytrigger`$$

CREATE TRIGGER `mytrigger` BEFORE INSERT on `t1`
FOR EACH ROW
BEGIN
	SET NEW.valor = (SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE
	TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 't1') * 200;
END$$

DELIMITER ;

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.37-community MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> INSERT INTO t1 (fecha) VALUES (NOW());
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM t1;
+----+---------------------+-------+
| id | fecha               | valor |
+----+---------------------+-------+
|  1 | 2007-03-11 03:31:22 |   200 |
+----+---------------------+-------+
1 row in set (0.00 sec)

mysql> INSERT INTO t1 (fecha) VALUES (NOW());
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1;
+----+---------------------+-------+
| id | fecha               | valor |
+----+---------------------+-------+
|  1 | 2007-03-11 03:31:22 |   200 |
|  2 | 2007-03-11 03:31:31 |   400 |
+----+---------------------+-------+
2 rows in set (0.00 sec)