get field value after event triggered

[ads42]

Hi,
I have to post datas on a table where the id of a field (uuid) is set by a trigger.
How can I get the value of this field after calling MyTable.post ?
Refresh change the record position.
Regards

[DemetrionQ]

Hello.

MySql server does not return information about fields filled by the trigger during data change, thus TMyTable doesn't let getting this information either.
To solve this problem, you can use an autoincrement field or a unique key with value set on the client.

[ads42]

the solution I have found for now is :
Bookmark := theTable.GetBookmark;
theTable.Refresh;
theTable.GotoBookmark(Bookmark);

[DemetrionQ]

Hello.

GotoBookmark moves to a record taking into account only its number in your dataset. As a result, your method can not work in the following situations:
- you are using ORDER BY;
- another user (or just another TMyTable, TMyQuery ) has inserted a new record before your TMyTable.Refresh operation.
That's why we recommend using an autoincrement field or a unique key with value set on the client.

[ads42]

yep, I've seen that bookmark was a bad idea.
Autoincrement is not possible also since I have to sync database with other database that are in multiple system, using autoinc will create duplicate id....

[DemetrionQ]

Hello.

To solve the problem, you can transform the field, which you modify with a trigger, to autoincrement, and set an appropriate start value to it. You can do this using the following SQL instructions:

Code: Select all

ALTER TABLE tablename MODIFY id_fieldname INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;
ALTER TABLE tablename AUTO_INCREMENT = 42;
/*instead of "42" set the needed start value for autoincrement counter*/