Mike Kruckenberg wrote:
mysql SET @staff_id = LAST_INSERT_ID();
Query OK, 0 rows affected (0.01 sec)
I don't know if this behaviour has changed in later versions of mysql,
but using session variables, although lovely, was the quickest way to
break replication (at least up to and including
Hi List,
Let's suppose I have these two tables:
CREATE TABLE `changes` (
`ID` int(12) unsigned NOT NULL auto_increment,
`Key` varchar(25) collate latin1_general_cs NOT NULL default '',
`Table` varchar(25) collate latin1_general_cs NOT NULL default '',
`Value` text collate
Andre Matos wrote:
The idea is to have a audit trail to record the changes made. So, I want to
insert a new record in the staff table and right after this, insert a
record in the changes table.
SET AUTOCOMMIT=0;
START TRANSACTION;
INSERT INTO staff (`Name`) VALUES ('ABC');
INSERT INTO changes
Andre Matos wrote:
SET AUTOCOMMIT=0;
START TRANSACTION;
INSERT INTO staff (`Name`) VALUES ('ABC');
INSERT INTO changes (`Key`, `Table`, `Value`) VALUES (LAST_INSERT_ID(),
'staff', 'ABC');
COMMIT;
SET AUTOCOMMIT=1;
This works fine in my test environment, however what about many users doing
at
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
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,
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
