List:General Discussion« Previous MessageNext Message »
From:W. D. Date:October 18 2012 4:46am
Subject:Re: Possible to copy the key field to another on INSERT?
View as plain text  
At 02:44 10/17/2012, Claudio Nanni wrote:
>Take a look at TRIGGERS
>
>C.

Thanks Claudio.

I wrote a trigger that MySQL accepted.  However, when
I tried to insert a new record:

  "Can't update table 'tbl' in stored function/trigger 
   because it is already used by statement which invoked 
   this stored function/trigger"

I Googled the error:

   http://dev.mysql.com/doc/refman/5.5/en/faqs-triggers.html#qandaitem-B-5-1-9

   "B.5.9: Can triggers access tables?"

   "A trigger can access both old and new data in its own 
   table.  A trigger can also affect other tables, but it 
   is not permitted to modify a table that is already 
   being used (for reading or writing) by the statement 
   that invoked the function or trigger."

From this it seems that a trigger will not allow me to 
auto-populate the `AssociatedWith` field the value of
the `KeyField`.

It looks like I will have to populate that field immediately
after creating the record with an additional statement, yes?

Does anyone have any other ideas on how to populate the
value of one field with the value of the `KeyField' on
creation(INSERT)?

I want to be able to search on the `AssociatedWith` field
to find all records that rely on that first record's 
`KeyField`.





>
>PS: I am curious to know why you would do that anyway
>
>2012/10/17 W. D. <WD@stripped>
>
>> When creating a record, the first field (KeyField)...
>>
>>   KeyField        BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
>>
>> ...is it possible to copy this auto-generated value into
>> another field when using the same INSERT that creates the record?
>>
>> Or would I have to use an UPDATE query using LAST_INSERT_ID()
>> immediately after the INSERT statement?
>>
>> Thanks for any ideas you have.











Start Here to Find It Fast!™ -> http://www.US-Webmasters.com/best-start-page/
$9.99 Domain Names -> http://domains.us-webmasters.com/

Thread
Possible to copy the key field to another on INSERT?W. D.17 Oct
Re: Possible to copy the key field to another on INSERT?Dehua Yang17 Oct
  • Re: Possible to copy the key field to another on INSERT?Shawn Green17 Oct
    • Re: Possible to copy the key field to another on INSERT?Dehua Yang17 Oct
Re: Possible to copy the key field to another on INSERT?Claudio Nanni17 Oct
  • Re: Possible to copy the key field to another on INSERT?W. D.17 Oct
  • Re: Possible to copy the key field to another on INSERT?W. D.18 Oct