Andre Matos wrote:
Thanks Mike.
I understand the possible "gaps" that I might have if I use the ROLLBACK.
This is acceptable in my case.
What I really want to avoid is what I am doing now: open one transaction to
insert, or update, or delete certain information and close with the commit.
Then, I get the LAST_INSERT_ID() and open another transaction to write my
audit trail. However, if the first one went through ok but if I got a
problem at the second transaction, I need to delete the inserted or updated
or move back the deleted information. This doesn't work well.
Let's expand my "staff" and "change" tables to have this structure to
simulate my problem:
+----+------+--------+
| ID | Name | Gender |
+----+------+--------+
+----+-----+-------+-------+-------+
| ID | Key | Table | Field | Value |
+----+-----+-------+-------+-------+
And do this:
SET AUTOCOMMIT=0;
START TRANSACTION;
INSERT INTO staff (`Name`, `M`) VALUES ('ABC');
INSERT INTO changes (`Key`, `Table`, `Field`, `Value`) VALUES
(LAST_INSERT_ID(), 'Staff', 'ID', LAST_INSERT_ID());
INSERT INTO changes (`Key`, `Table`, `Field`, `Value`) VALUES
(LAST_INSERT_ID(), 'Staff', 'Name', 'ABC');
INSERT INTO changes (`Key`, `Table`, `Field`, `Value`) VALUES
(LAST_INSERT_ID(), 'Staff', 'Gender', 'M');
COMMIT;
SET AUTOCOMMIT=1;
I will get something like this:
mysql> select * from staff;
+----+------+--------+
| ID | Name | Gender |
+----+------+--------+
| 1 | ABC | M |
+----+------+--------+
1 row in set (0.00 sec)
mysql> select * from changes;
+----+-----+-------+-------+-------+
| ID | Key | Table | Field | Value |
+----+-----+-------+-------+-------+
| 1 | 1 | staff | ID | 1 |
+----+-----+-------+-------+-------+
| 2 | 1 | staff | Name | ABC |
+----+-----+-------+-------+-------+
| 3 | 2 | staff | Gender| M |
+----+-----+-------+-------+-------+
3 row in set (0.00 sec)
See that I have a problem in the third line at the "Key" column where I
should have "1" but I got "2" instead. This happened because of
LAST_INSERT_ID() used the ID from the "changes" table instead of the desired
"staff" table.
Is there any way to avoid this? What about the mysql_insert_id()?
I see. In this case you could make it two operations and use the
mysql_insert_id() to capture the id from the first insert, setting a
variable to that in PHP and using that variable to ensure the same number.
What I would consider is setting a database variable inside the
transaction to store the id - the @ signifies it's a session variable
that is specific to this connection:
SET AUTOCOMMIT=0;
START TRANSACTION;
INSERT INTO staff (`Name`, `Gender`) VALUES ('ABC','M');
SET @staff_id = LAST_INSERT_ID();
INSERT INTO changes (`Key`, `Table`, `Field`, `Value`) VALUES
(@staff_id, 'Staff', 'ID', @staff_id);
INSERT INTO changes (`Key`, `Table`, `Field`, `Value`) VALUES
(@staff_id, 'Staff', 'Name', 'ABC');
INSERT INTO changes (`Key`, `Table`, `Field`, `Value`) VALUES
(@staff_id, 'Staff', 'Gender', 'M');
COMMIT;
SET AUTOCOMMIT=1;
So you store the value after the first insert and then reuse. You can
see it in the following string of SQL commands to demonstrate:
mysql> INSERT INTO staff (`Name`, `Gender`) VALUES ('ABC','M');
Query OK, 1 row affected (0.01 sec)
mysql> SET @staff_id = LAST_INSERT_ID();
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT @staff_id;
+-----------+
| @staff_id |
+-----------+
| 3 |
+-----------+
1 row in set (0.00 sec)
mysql> INSERT INTO changes (`Key`, `Table`, `Value`) VALUES
(LAST_INSERT_ID(), 'staff', 'ABC');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO changes (`Key`, `Table`, `Value`) VALUES
(LAST_INSERT_ID(), 'staff', 'ABC');
Query OK, 1 row affected (0.00 sec)
mysql> select LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 5 |
+------------------+
1 row in set (0.00 sec)
mysql> SELECT @staff_id;
+-----------+
| @staff_id |
+-----------+
| 3 |
+-----------+
1 row in set (0.00 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
