I have a trigger for DELETE for table1. This trigger deletes some more rows in table1. After doing that, the trigger calls a stored procedure, SP1, that works with table2, selecting rows from table1 and inserting them in table2.
The problem is the following: If I run SP1 alone, I can do selects (using with (nolock)) from the table2 while SP1 is still running. But if the trigger for DELETE executes SP1, I can't do any selects on table2 while SP1 is running (and was called by the DELETE trigger).
I set the lowest isolation level (read uncommited) both in the trigger and in the stored procedure, but I can't still read things from table2 when SP1 is called from the delete trigger. Why is this happening? Is there any way to solve it? I don't care about dirty reads, nor about nonrepeatable reads.