>-----Original Message-----
>From: Michael Dykman [mailto:[email protected]]
>Sent: Friday, January 21, 2011 1:27 PM
>To: Jerry Schwartz
>Cc: MySql
>Subject: Re: CURRENT insert ID
>
>You don't need to do an update:
>
>...
>
>new.xxx = new.id
>...
>
[JS] I wish it were that easy. new.id is null until after the INSERT has
completed:
SHOW CREATE TABLE xxx\G
*************************** 1. row ******************
Table: xxx
Create Table: CREATE TABLE `xxx` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`vv` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
SHOW CREATE TRIGGER foo\G
*************************** 1. row ***************************
Trigger: foo
sql_mode:
STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`access`@`%` TRIGGER foo BEFORE UPDATE
ON xxx
FOR EACH ROW
SET NEW.vv = NEW.id
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
INSERT INTO xxx VALUES (NULL,NULL);
SELECT * FROM xxx;
+----+------+
| id | vv |
+----+------+
| 1 | NULL |
+----+------+
1 row in set (0.00 sec)
I'm tearing my hair out.
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
E-mail: [email protected]
Web site: www.the-infoshop.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[email protected]
