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


-----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]