Last post Aug 30, 2018 05:30 AM by wmec
Aug 29, 2018 07:00 AM|nambir|LINK
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.
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
/*DISABLE TRIGGER Table1Trigger*/
ALTER TABLE Table1 DISABLE TRIGGER Table1Trigger
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
Aug 30, 2018 03:11 AM|Nan Yu|LINK
When updating the tables in Transaction , it will lock that resource(if you have suitable isolation level), That means when you use transactions, you put locks on data that is pending for permanent change to the database. No other operations can take
place on locked data until the acquired lock is released. You could lock anything from a single row up to the entire database :
Aug 30, 2018 05:30 AM|wmec|LINK
Trigger is the way to do automatic change to table. It is not good practice to disable/enable that in programmable way.