Jump to content
  • 0

Return Primary Key of Newly Inserted Record in Trigger



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.

Link to comment
Share on other sites

3 answers to this question

Recommended Posts

  • 0

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

Link to comment
Share on other sites

  • 0

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.

Link to comment
Share on other sites

  • 0

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


Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...