Return the block_id from transaction group after execution

I would like to set up some relational tables , and preferably use Ignitions transaction groups to do so.
But i’m running into a bit of a problem with the conceptual idea.
I would like to insert a row into a table that represents a production item. I would like to be able to use the auto incrementing column of the table as a unique ID for this item, which i can then use in other transaction groups that may provide relational data.
I don’t see a way to extract this though.
I will need this value to be used in a PLC which is compiling the relational data.

Is the only option to use an SQL query tag which reads the MAX(block_id) out? How could i write that to the PLC though?

I would use a stored procedure group. Have your stored procedure return the new ID as an OUT parameter.

Thanks Phill I’ll look into using a stored procedure.

I’ve acually got a slightly different use case pop up which i’m not sure i can achieve with a stored procedure.

I have a Block Transaction group which writes a block of data to SQL on a trigger. There are 500 rows, 3 columns. Each trigger is inserting a new block and hence generating a new block_id.
I would like to get this new block_id into the PLC somehow.

It doesn’t look like Stored Procedure groups support block inserts though.

At this stage i’m thinking of having two tags to achieve this.
SQL tag to read MAX(block_id) from table.
OPC tag to write to PLC.

Add an on-change script to the SQL tag, if value changes, write it to the OPC tag.
Then the PLC can detect the new block_id and act appropriately.