I was just wondering if something I was looking to do was doable or not.
I have a stored procedure that will first insert a record into a table (MO_ITEMS) and increment the primary key/generator accordingly.
I then want to take the newly created PK and insert that into another table which will be used as a cross reference (MO_ITEM_CATS).
Here is what I have so far to create the procedure and a trigger for the generator:
CREATE GENERATOR ITEM_ID_GEN;
SET TERM ^ ;
CREATE TRIGGER CREATE_ITEM_ID FOR MO_ITEMS
NEW.ITEM_ID = GEN_ID(ITEM_ID_GEN, 1);
CREATE PROCEDURE NEW_ITEM
Details BLOB SUB_TYPE TEXT SEGMENT SIZE 80,
INSERT INTO MO_ITEMS (ITEM_ID, VENDOR, ITEM_NAME, DETAILS, COST, SHOW_ON_MAIN, IS_ACTIVE)
VALUES (0, :VendorID, :ItemName, :Details, :Cost, :ShowMain, :IsActive);
INSERT INTO MO_ITEM_CATS (ITEM_ID, CAT_ID) VALUES (:ItemID, :CatID);
SET TERM ; ^
Will I need to set an output parameter for the ITEM_ID or is there a quicker way. I know SQL Server has the @@IDENTITY property and I'd like to do something like that.