Jump to content
  • 0

On Insert Need to Add 12 Records to Child Table


Becca37
 Share

Question

On Insert into a Client table I need to (a) insert a record into an audit log and (b) add 1 child record to an Admissions table.  Both parts (a) and (b) are working fine.

On the insert to the Admissions table I need to (a) insert a record into an audit log and (b) add 12 child records into an AdmissionChecklistItems table.  Part (a) is working fine, but for part (b) I'm limited to ONE (1) child record due to the limitations on "cascading triggers". It all worked fine with the audit log insert and a test with 1 checklist item insert. Then I went whole hog and added the other 11 items and {bam!} error message. It was all for naught, I already had reached the limit (sure wish it wouldn't let us add more than it will let us run!).

On the insert to the AdmissionChecklistItems table I need to insert a record into an audit log. This is working fine.

~~~~~~~~~~~

I do have the checklist items in a table. Effectively I need the second INSERT of the trigger to be ...

INSERT into AdmissionChecklistItems SELECT ([RID from #inserted], SelectValue FROM Lists WHERE Category = 'AdmissionChecklistItems')

... which could insert all 12 checklist items in one insert statement? I know this can be done in Transact-SQL but I'm feeling hampered by the Caspio structure.

IS there a way I can do this in Caspio? If not what's the suggested workaround to get this done in an automated manner?  

2019-04-01_17-25-19.thumb.png.4fcfd5be2e913c7ff257d7b9c8446223.png

Link to comment
Share on other sites

8 answers to this question

Recommended Posts

  • 0

Hi @Becca37,

 

As I understand, you want to insert 12 records in AdmissionChecklistItem table upon inserting a record in Admission Table. May I ask if you were inserting 12 identical records? 

If yes, there is a workaround for this. You need to create a Dummy table. This will helps with the looping of the trigger.

image.png.4bf207b3610ae4b17f47b31397d3c9c5.png

 

The trigger on your Admission Table should look like this:

 

image.thumb.png.03c8b2db126b7b6b8bf704e459527648.png

 

You may import this file to your account and see how it works. Add_12_Records_1_0_2019-Apr-01_2106.zip

 

Hope this helps. 

 

-kristina

 

Link to comment
Share on other sites

  • 0

@kristina  The 12 records to be inserted are all different and I have them in a lookup table. The Client will be managing them and we don't want to have to modify the backend everytime they change a checklist item. Your method worked great on a test table, with modification to pull the values from the lookup table. You're awesome! So that's one thing resolved. :0)

But it seems I misinterpreted the error message that I was getting. Looking at it with fresh eyes this morning, along with  the fact that I'm still getting the error when I move from my test tables to my real tables, I'm thinking "exceeds limit of 2 cascading triggers" means I can't do:

(1) Insert into Clients triggers insert into audit and Admissions

(2) Insert into Admission triggers insert into audit and AdmissionChecklistItems

(3) Insert into AdmissionChecklistItems triggers insert into audit

So I'll have to have the user do two steps, which is fine as that's what they're doing now I was just hoping to automate the second step. 

Still, it's a shame CASPIO limits cascading triggers to 2. SQL Server allows 30+ and Oracle allows 60+. 2 is extremely limiting in a relational database, IMO.

ExceedsLimitCascadingTriggers.thumb.png.9416835fa69c114671087dc91bb2c7e9.png

2019-04-02_6-53-21.thumb.png.5ec9a150364aeca9b3f0f7a820c1a46a.png

 

Link to comment
Share on other sites

  • 0
7 hours ago, kristina said:

Hi @Becca37,

 

The error "Triggered Action cannot be enabled because it exceeds the limit of 2 cascading triggers." happens when you are creating triggered action on the same events.  Oh yeah, understood! Just idly wondering now why Caspio limits it so drastically to 2  since SQL Server allows 30+ and Oracle 60+ cascading triggers.

You may try to check this forum post for more details: 

Right now, can you please try this one: I tried that before when I was updating the trigger after our first discussion. But the #inserted has to be in that select statement as most of the fields I'm actuallly inserting into the child table are from the #inserted table. :0)

Hope this helps. 

 

-kristina

 

2019-04-04_5-57-05.png

Link to comment
Share on other sites

  • 0

Hi @Becca37,

Caspio only allows a limit of 2 cascading triggers. That being said, it seems that you already exceeded this limit. 

 

As you mentioned, these are the events occuring on your cascading triggers

"(1) Insert into Clients triggers insert into audit and Admissions

(2) Insert into Admission triggers insert into audit and AdmissionChecklistItems

(3) Insert into AdmissionChecklistItems triggers insert into audit"

 

Due to the system limit, you may want to rethink the structure of the trigger. 


My first question is, the trigger that you are trying to implement, can it be compressed or joined to the prior triggers? 

For example, if trigger one inserts to table 2. and table 2 has trigger that inserts the inserted record to table 3. Would it be possible if we joined these triggers on trigger 1 itself?

 

I do not have an overview with your triggers, but it could be helpful if you would explain how it works. 

 

I hope this helps. 

Link to comment
Share on other sites

  • 0

Hi @Glitch ... thanks for your response. We already got this sorted, I broke things out into two manual steps for the client instead of the desired one. 

Still, it's a shame CASPIO limits cascading triggers to 2. SQL Server allows 30+ and Oracle allows 60+. 2 is extremely limiting in a relational database, IMO.

Link to comment
Share on other sites

  • 0

Hi,

If someone else face this limitation, I can recommend using API coding to get any amount of records you need created in your tables. 

In case you are not an API savvy, you can still create your own logic by using Integromat (a service that lets you connect with dozens of apps without having to write code), you could even set up .csv uploads directly into Caspio tables.

Hope it helps.

Regards.

Link to comment
Share on other sites

  • 0
On 4/15/2019 at 1:29 PM, douvega said:

Hi,

If someone else face this limitation, I can recommend using API coding to get any amount of records you need created in your tables. 

In case you are not an API savvy, you can still create your own logic by using Integromat (a service that lets you connect with dozens of apps without having to write code), you could even set up .csv uploads directly into Caspio tables.

Hope it helps.

Regards.

Hi @douvega The number of records to be created wasn't the issue. It was the number of cascading triggers that caused the error. All resolved. Thanks though, APIs are an area I need to explore soon regardless. :0)

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.

Guest
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.

Loading...
 Share

×
×
  • Create New...