Return Primary Key of Newly Inserted Record in Trigger

[JayDub]

Hi - I'm trying to create a trigger that will do the following:

When a new record is inserted into Table A, a trigger will fire off inserting x number of records into Table B from Table C. Table A and Table C can be joined via a common field, so I am able to select the "x" number of records to insert into Table C fairly easily. However, I'm getting stuck on the second part of what I want the trigger to do. When each new record is inserted into Table C, I would like to get the Primary Key field value back from that insert and write it along with the primary key of Table A into another table, Table D. In a SQL Server Stored Procedure, I believe you could use a statement like:

Select @n1 = n1 from inserted a

To retrieve the primary key when inserting a new record. I'm thinking if Caspio has something similar, I can use a Loop to insert each new record into Table C one at a time, reading back the Primary Key each time and then inserting a new record into Table D. Alternatively, I believe SQL has a SCOPE IDENTITY function that also retrieves the PK of the newly inserted record. Anything similar in Caspio?

BTW - I considered having a second, separate trigger in Table C to insert the record into Table D, but that won't work, because sometimes records will be added manually to Table C and in this case, new records should not be created in Table D.

[NiceDuck]

I dont understand much of your inquiry, but If I need to get the Id of the record I inserted on another table using a trigger, we will need to mark it so we can use that mark as an Indicator. Kind of like this:


If you can provide more details about what are you trying to achieve, perhaps there is something more I can suggest
 

[JayDub]

Hi @NiceDuck. Thank you for your suggestion. And my apologies if my explanation wasn't clear. I confirmed in a chat with Caspio support that there is not built-in way to retrieve a record's primary key when inserting into a different table while in a trigger. So I used a workaround, somewhat similar to what you suggested, by using a marker on each record that I could identify when I went to do the insert into the next table.

[Sam23]

This is exactly what I need, but not understanding completely. Say we have two tables: Parent and Child.

When I insert a record into Parent, I need a record in Child table with the ParentID which is not available until after the trigger runs.

On a Parent insert trigger, I insert record into Child and store that ChildID in the Parent table (Update Parent with that value)? 

From there, how do I get the ParentID into the Child table? I could do a Child Update and use a join with the Parent table on that ChildID stored in the Parent table, but it still can't retrieve the ParentID until after the trigger runs. Right?

Thanks for helping

Sam