Thanks for all your help Mike.
Problem solved. I divided to process in two parts: one write the
insert/update/delete and then write the changes in the audit trail. All this
inside one transaction. If the first part fails, ROLLBACK. If the second
part fails, ROLLBACK, otherwise, if both were done ok, then COMMIT.
I just wanted to have all this in only one part, but that's fine. It's
working fine.
Final question: Can I create an audit trail using TRIGGER in MySQL 5? This
would be the best because any changes in the database (insert/update/delete)
will start the trigger which will be responsible for writing the audit
trail.
Thanks again!!!
Andre
On 11/28/06 9:22 PM, "Mike Kruckenberg" <[EMAIL PROTECTED]> wrote:
> 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]
