Disable Trigger in Transaction RRS feed

  • Question

  • User2048898515 posted

    Hi Guys,

    For my scenario i need to Perform Update script in Transaction in SP(Stored Procedure). But i need to disable the trigger at the beginning of the SP and Enable at the end of SP

    Reason for Disabling Trigger:i need to overcome InsertConflict.

    in SP i am updating 10 tables.

    My Question:

    when this SP is getting executed by User1, what will happen if some other User ie user2 is trying to update Table1  from WebClient.

    will the Update from User2 will wait till the SP execution to complete because it is in transaction? Reason i am asking is because, since we disable the trigger in the beginning at that moment, update script from User2 should not be fired because it will lose the trigger operation.

    Alter PROCEDURE UpdateUserIDs
    @UserID int, 
    @NewID int
    	/*DISABLE TRIGGER Table1Trigger*/
      ALTER TABLE Table1 DISABLE TRIGGER Table1Trigger
      /*Update Script*/
      update Table1 set @UserID=@NewID where userId=@UserID
      update Table2 set @UserID=@NewID where userId=@UserID
      update Table3 set @UserID=@NewID where userId=@UserID
      	/*Enable TRIGGER Table1Trigger*/
      ALTER TABLE Table1 ENABLE TRIGGER Table1Trigger

    Wednesday, August 29, 2018 7:00 AM

All replies