asked
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
Are you just curious about the locking order and types, or is this causing you an issue?
ok so what is the problem that it creates? Are you getting any error messages or behaviour?
Hi, situation is following: Insert into Table1 fired 2 triggers. Trigger 1 updates Table2 and Trigger2 updates Table3. BUT Table2 has also a trigger Table2.trigger1 which Updates the same field in Table3 as Table1.Trigger2 Problem is that data into Table 1 and Table 2 may be inserted form different parts of the system and not always into both at the same time. So Table 3 have to be updated for changes in both Tables. Any advise?
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 .
22 People are following this question.
