472,096 Members | 1,467 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,096 software developers and data experts.

select last_insert_id()

201 100+
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:
Expand|Select|Wrap|Line Numbers
  1. -- Table: tblaudit 
  3. -- DROP TABLE IF EXISTS `tblaudit`; 
  5. CREATE TABLE `tblaudit` ( 
  6. `AuditId` int(8) NOT NULL, 
  7. `UserName` varchar(50) NOT NULL, 
  8. `TableName` varchar(50) NOT NULL, 
  9. `FieldName` varchar(50) NOT NULL, 
  10. `OldValue` longtext , 
  11. `NewValue` longtext , 
  13. `TestId` bigint AUTO_INCREMENT NOT NULL, 
  14. /* Keys */ 
  15. PRIMARY KEY (`TestId`) 
  16. ) ENGINE = InnoDB 
  17. CHARACTER SET `utf8` COLLATE `utf8_unicode_ci`; 
here is the procedure code
Expand|Select|Wrap|Line Numbers
  3. CREATE PROCEDURE `add_tblAudit` 
  4. IN `pUserName` varchar(50), 
  5. IN `pTableName` varchar(50), 
  6. IN `pFieldName` varchar(50), 
  7. IN `pOldValue` longtext, 
  8. IN `pNewValue` longtext 
  9. BEGIN 
  10. DECLARE insertId; 
  11. INSERT INTO `tblAudit` (`UserName`, `TableName`, `FieldName`, `OldValue`, `NewValue`) 
  12. VALUES (pUserName, pTableName, pFieldName, pOldValue, pNewValue); 
  13. SELECT last_insert_id() INTO insertId; 
  14. INSERT INTO `tblAudit`(`TestId`) VALUES (insertId); 
  15. END| 
  17. DELIMITER ; 
thanks for your help in advance!
Jan 24 '09 #1
2 4276
201 100+
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!



Jan 25 '09 #2
5,058 Expert 4TB
Hi Wiz.

Glad you found a solution.
Thanks for sharing it.
Jan 25 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by mr_burns | last post: by
reply views Thread by Irene Mettias | last post: by
1 post views Thread by Michal Adamczakk | last post: by
6 posts views Thread by jim | last post: by
1 post views Thread by serena.delossantos | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.