469,631 Members | 1,209 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,631 developers. It's quick & easy.

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 
  2.  
  3. -- DROP TABLE IF EXISTS `tblaudit`; 
  4.  
  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 , 
  12. `DTAdded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 
  13. `TestId` bigint AUTO_INCREMENT NOT NULL, 
  14. /* Keys */ 
  15. PRIMARY KEY (`TestId`) 
  16. ) ENGINE = InnoDB 
  17. CHARACTER SET `utf8` COLLATE `utf8_unicode_ci`; 
  18.  
here is the procedure code
Expand|Select|Wrap|Line Numbers
  1. DELIMITER | 
  2.  
  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| 
  16.  
  17. DELIMITER ; 
  18.  
thanks for your help in advance!
Jan 24 '09 #1
2 4185
wizardry
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!

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

Thanks,

Wiz
Jan 25 '09 #2
Atli
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
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.