RE: Retrieving last_insert_id

>-----Original Message-----
>From: Al McNicoll [mailto:[email protected]]
>Sent: Thursday, February 03, 2011 10:33 AM
>To: 'Jerry Schwartz'; [email protected]
>Subject: RE: Retrieving last_insert_id
>
>>> [JS] A trigger won't let you modify the table that is associated with the
>trigger. For example, you can't UPDATE the table you did an INSERT into.
>[ARM] Is that definitely right? I thought with a BEFORE INSERT you could use
>the NEW table variable to change values. From the MySQL docs:
>
>"A column named with NEW can be referred to if you have the SELECT privilege
>for it. In a BEFORE trigger, you can also change its value with SET
>NEW.col_name = value if you have the UPDATE privilege for it. This means you
>can use a trigger to modify the values to be inserted into a new row or that
>are used to update a row."
>
>I don't know whether NEW.autoidfield will have a value at this stage though?
>
>Al
>
[JS] No, it won't.

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



>
>-----Original Message-----
>From: Jerry Schwartz [mailto:[email protected]]
>Sent: 03 February 2011 15:23
>To: 'Al McNicoll'; [email protected]
>Subject: RE: Retrieving last_insert_id
>
>>-----Original Message-----
>>From: Al McNicoll [mailto:[email protected]]
>>Sent: Thursday, February 03, 2011 4:45 AM
>>To: 'Jerry Schwartz'; [email protected]
>>Subject: RE: Retrieving last_insert_id
>>
>>Hi Jerry,
>>
>>There is always the possibility of using a trigger on INSERT - would that
>>solve
>>your problem? I think that prior to MySQL 5.1 you need SUPER privileges to
>>work
>>with triggers, after that it's a separate permission. I see no reason why
>an
>>INSERT trigger wouldn't pick up the correct value from the autoincremented
>>column. Alternatively, you could put the trigger on UPDATE (on UPDATE SET
>>b=autoidfield) and then simply run an UPDATE tbl SET
>autoidfield=autoidfield
>>after your INSERT - that should still throw the trigger, and because the
>>row's
>>already been inserted, the autoincremented value will be set and
>retrievable.
>>
>[JS] A trigger won't let you modify the table that is associated with the
>trigger. For example, you can't UPDATE the table you did an INSERT into.
>
>>Otherwise, what isn't working when you SELECT LAST_INSERT_ID() via MyODBC?
>I
>>code a global function into all my VBA/MyODBC projects that takes a ADODB
>>connection and returns the last insert id just by running that select. As
>>long
>>as you maintain the same connection object for the INSERT and the
>subsequent
>>SELECT (or in your case UPDATE) then last_insert_id should be retrieved
>>correctly.
>>
>[JS] I wasn't using an ADO connection. I'm using a form's events. The
>logical
>place to put this was in the AfterInsert event handler. I think that the
>form's inner workings open and close the connection.
>
>Unfortunately, the MyODBC traces only capture the actual queries, so I'm not
>
>sure what is really going on. The workings of Access are mysterious indeed,
>and it watches everything you do very closely. I didn't do the experiment,
>but
>I think that trying to update that field with anything would trigger the
>form's update events, and I could get endless recursion.
>
>>If not, could you post back what you do get?
>
>[JS] I will if I have time. As I recall, I got back 0 in this particular
>case.
>>
>>Hope that helps,
>>
>[JS] Thanks for the thoughts.
>
>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
>
>
>
>>Al
>>
>>
>>-----Original Message-----
>>From: Jerry Schwartz [mailto:[email protected]]
>>Sent: 21 January 2011 19:57
>>To: [email protected]
>>Subject: Retrieving last_insert_id
>>
>>I have an auto_increment field in my table, but I need to set another field
>
>>to
>>the same value. (Please don't ask why.)
>>
>>
>>
>>It seems I would need to insert the record, and then update it; but I
>haven't
>>figured out how to get the value of last_insert_id().
>>
>>
>>
>>Any ideas?
>>
>>
>>
>>Regards,
>>
>>
>>
>>Jerry Schwartz
>>
>>Global Information Incorporated
>>
>>195 Farmington Ave.
>>
>>Farmington, CT 06032
>>
>>
>>
>>860.674.8796 / FAX: 860.674.8341
>>
>>E-mail:  <mailto:[email protected]> [email protected]
>>
>>Web site:  <http://www.the-infoshop.com/> www.the-infoshop.com
>>
>>
>>
>
>
>





-- 
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe:    http://lists.mysql.com/[email protected]