On Insert Need to Add 12 Records to Child Table

[Becca37]

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?  

[cheonsa]

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.

 

The trigger on your Admission Table should look like this:

 

 

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

 

[Becca37]

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

 

[cheonsa]

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. 

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

 

Right now, can you please try this one:

 

Hope this helps. 

 

-kristina

[Becca37]

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

 

[Glitch]

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. 

[Becca37]

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.

[douvega]

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.

[Becca37]

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)