locked
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
    AS 
    
    BEGIN
        BEGIN TRANSACTION;
    	/*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
    
       COMMIT TRANSACTION 
    END;
    Go
    

    Wednesday, August 29, 2018 7:00 AM

All replies