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/
