List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:January 21 2011 8:06pm
Subject:RE: CURRENT insert ID
View as plain text  
>-----Original Message-----
>From: Michael Dykman [mailto:mdykman@stripped]
>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: jerry@stripped
Web site: www.the-infoshop.com



Thread
CURRENT insert IDJerry Schwartz21 Jan
  • Re: CURRENT insert IDjoao21 Jan
    • Re: CURRENT insert IDDarryle Steplight21 Jan
  • Re: CURRENT insert IDjoao21 Jan
    • RE: CURRENT insert IDJerry Schwartz21 Jan
      • Re: CURRENT insert IDDonovan Brooke22 Jan
        • Re: CURRENT insert IDJesper Wisborg Krogh22 Jan
        • RE: CURRENT insert IDJerry Schwartz24 Jan
          • Re: CURRENT insert IDMark Goodge24 Jan
      • Re: CURRENT insert IDJaime Crespo Rinc√≥n24 Jan
  • Re: CURRENT insert IDCarsten Pedersen23 Jan
    • Re: CURRENT insert IDCarsten Pedersen23 Jan
    • RE: CURRENT insert IDJerry Schwartz24 Jan
Re: CURRENT insert IDMichael Dykman21 Jan
  • RE: CURRENT insert IDJerry Schwartz21 Jan
    • RE: CURRENT insert IDJerry Schwartz21 Jan
      • RE: CURRENT insert IDJerry Schwartz21 Jan
      • Re: CURRENT insert IDMichael Dykman21 Jan
        • RE: CURRENT insert IDJerry Schwartz21 Jan