select last_insert_id()

[wizardry]

Hello -

I'm recently a newbie to mysql, but have experiance in oracle, mssql, access, etc...

Here is my problem, i've created the table needed to test the audit procedures. the data element that the id is going to be inserted into is auto_increment.

I don't know what the problem could be.

here is the table code:

-- Table: tblaudit 
 
-- DROP TABLE IF EXISTS `tblaudit`; 
 
CREATE TABLE `tblaudit` ( 
`AuditId` int(8) NOT NULL, 
`UserName` varchar(50) NOT NULL, 
`TableName` varchar(50) NOT NULL, 
`FieldName` varchar(50) NOT NULL, 
`OldValue` longtext , 
`NewValue` longtext , 
`DTAdded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 
`TestId` bigint AUTO_INCREMENT NOT NULL, 
/* Keys */ 
PRIMARY KEY (`TestId`) 
) ENGINE = InnoDB 
CHARACTER SET `utf8` COLLATE `utf8_unicode_ci`; 
 

here is the procedure code

DELIMITER | 
 
CREATE PROCEDURE `add_tblAudit` 
( 
IN `pUserName` varchar(50), 
IN `pTableName` varchar(50), 
IN `pFieldName` varchar(50), 
IN `pOldValue` longtext, 
IN `pNewValue` longtext 
) 
BEGIN 
DECLARE insertId; 
INSERT INTO `tblAudit` (`UserName`, `TableName`, `FieldName`, `OldValue`, `NewValue`) 
VALUES (pUserName, pTableName, pFieldName, pOldValue, pNewValue); 
SELECT last_insert_id() INTO insertId; 
INSERT INTO `tblAudit`(`TestId`) VALUES (insertId); 
END| 
 
DELIMITER ; 
 

thanks for your help in advance!

[wizardry]

disregard this request!

the solution is as follows:

in the insert into values place last_insert_id() where the field should go for the value that your inserting. also ensure that the audit table you are inserting to has the primary key on this field and is auto_inc.

I've found what i want to acomplish here!

http://www.paragon-cs.com/mag/issue6.pdf

Thanks,

Wiz

[Atli]

Hi Wiz.

Glad you found a solution.
Thanks for sharing it.