What will be the Chain of locks(sql2016):the table has trigger and check constraint to another table
Hi, We have a lot of locks in the system which involved 4 tables but not sure how to resolve it without changing business rules. Insert into Table1 happened within Begin - Commit transaction. Table1 has 2 triggers which updates table2 and table3. It also has Check constraint which check table4. Table 2 also has a trigger which will fire on update and constraint which check Table4. Can somebody explain the order and types of the locks for above situation. Thank you. Regards, Natalia
SELECT OBJECT_NAME(P.object_id) AS TableName, Resource_type, request_session_id FROM sys.dm_tran_locks L join sys.partitions P ON L.resource_associated_entity_id = p.hobt_id WHERE OBJECT_NAME(P.object_id) in( 'Table1,'Table2','Table3','Table4') use this query to get the table locks and analyse the issue. '
as per your explanation, need to track the table2 changes which are executed outside of the Table1.Trigger1,since table1 changes will always impact table2 .So modify the Table2.trigger1 code to check Table1 insertion happened or not before updating table3 .