MySQL Stored Procedure add row and return key

Question

The question I have been asked for this assignment is:

Using a sample data, add a new incident and a call record in a single transaction.

However I know it doesn't read to well and that's because of the language that my lecturer uses. But from what I gather he would like me to add a record to an incident table and a call log table that I have created.

The problem I am facing when trying to do this is getting the key out of the Incident table so I can add this also to a call log table to show which calls are linked to what incident. Does anyone have any idea how this could be achieved?

Stored Procedure so far

DDL for the two tables

Answer 1

I think you're looking for last insert id

Answer 2

You are probably looking for something like this:

begin transaction;
insert into <...>;
select LAST_INSERT_ID();
commit transaction;

although, I would be inclined to suggest this: don't use AUTO_INCREMENT unless you really need to. Rather, have a separate table with keys in it and use a simple key generator function/procedure to generate a new ID, then use that ID to insert a new record. That will provide you with much more fine-grained control over your database.