I’m struggling with triggers and indirect tables I need to mess with.

Context: We have an old app whose backend we can’t change but I have full access to the database.
This app is inserting one row in table A, then several rows in tables B, C, D.

I need to DELETE some rows from B, C, D that shouldn’t be there ONLY when the user is inserting a row in A.

So, I created an AFTER INSERT trigger for A, hoping to be able to delete rows in B, C, D.
That didn’t work, I even used WAITFOR DELAY long enough to wait for rows in B, C, D but apparently those are not visible when the trigger is running. My guess is that until the
transaction that inserted in A commits everything those new rows in B, C, D are not visible.

The question then is:

What is the best way to DELETE these rows in tables B, C, D at the time of inserting in A?

Is there a way to spawn an independent transaction that waits until it sees the rows in B, C, D and then deletes them?