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:
[code=mysql]
-- 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_TIMESTA MP,
`TestId` bigint AUTO_INCREMENT NOT NULL,
/* Keys */
PRIMARY KEY (`TestId`)
) ENGINE = InnoDB
CHARACTER SET `utf8` COLLATE `utf8_unicode_c i`;
[/code]

here is the procedure code
[code=mysql]
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`(`Tes tId`) VALUES (insertId);
END|

DELIMITER ;
[/code]
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.