asked
Lock Table, When Disabling, On Delete Triggers
Hello all, Id like to ask if I need to lock tables when I am disabling triggers in my script. I've got a script that deletes records from multiple tables. At the top of the script I am disabling all the triggers on the effected tables. e.g. DISABLE TRIGGER [dbo].[copyOnDelete] ON SampleTable GO DELETE * FROM SampleTable WHERE ID = 1 GO ENABLE TRIGGER [dbo].[copyOnDelete] ON SampleTable GO Id like to know if I should be locking table SampleTable for the duration of the script. Otherwise someone could delete a record from that table half way through running my script and the copyOnDelete trigger would not be fired. This will lead to data corruption in my database. Thanks people of the internet :)
You can go without any locking hints and without any selects when you execute the DDL statement inside a transaction as the DDL will put a **schema lock** on the table and any delete command will wait for the original transaction to complete. Also you have to begin the transaction prior disabling the trigger, because otherwise you are not guaranteed that some delete command will not be issued out of your transaction with disabled trigger. You can test it by simple example: --Start of sample. Run in SSMS window 1 CREATE TABLE SampleTable ( ID int PRIMARY KEY, Txt varchar(20) ) GO CREATE TRIGGER trg_SampleTable ON SampleTable AFTER DELETE AS BEGIN DECLARE @msg nvarchar(4000) SET @msg = STUFF((SELECT ';' + CAST(ID AS nvarchar(10)) FROM deleted FOR XML PATH('')), 1,1,'Deleted ID: '); PRINT @msg END GO INSERT INTO SampleTable(ID, Txt) SELECT 1, 'Val1' UNION ALL SELECT 2, 'Val2' UNION ALL SELECT 3, 'Val3' UNION ALL SELECT 4, 'Val4' GO BEGIN TRAN GO DISABLE TRIGGER trg_SampleTable ON SampleTable GO --Run the code above until this point in Window 1 --Run Next Commad in SSMS Window 2 --It will be blocked by previous transaction and will waint until the transaction ends DELETE FROM SampleTable WHERE ID IN (2,4); GO -- --Run next two statements in SSMS Window1 - it will delete the row with ID = 3 (Trigger will not be fired) and Enable trigger DELETE FROM SampleTable WHERE ID = 3 GO ENABLE TRIGGER trg_SampleTable ON SampleTable GO --Statement in SSMS Windows2 is still blocked --Run the Commit Command in Window1 COMMIT TRAN --After the commit you will see, tha the records 2 and 4 in SSMS window were deleted and the trigger has fired for them.
If your script needs to be run without affecting any other query, and without any other query affecting it, then put the whole lot into a transaction `BEGIN TRAN .... COMMIT`. This could cause some serious locking issues, and hurt concurrency, but depending on the length of the script and the amount of data modified, may be what you need? Alternatively break the script down into individual transactions that need to be isolated.
@DeDogs : right now I've had chance to test this, I'm going back to my original answer! Yes you can use 'GO' batch separators within the transaction. @Pavel 's answer shows a good example.
@Kev as you wrote, all needs to be executed inside a single transaction. Otherwise it is not guaranteed that other delete command will not be executed without enabled trigger. Even starting transaction after disable trigger as @WilliamD originally suggested will not work as other the delete command can be started between SQL processes the BEGIN Transaction and delete command. If the BEGIN TRANSACTION is executed prior the DISABLE TRIGGER, then a schema lock will be put on the table inside the tran and no other transaction can delete any row prior the transaction is commit or rolled back.
As @WilliamD says (beat me to it) you can put the disable and delete within a tran, then either rollback or commit and re-enable
One approach has been suggested here - this uses the context_info property of the connection to determine control-flow through the trigger. I don't particularly like the cleanliness of this solution, as the trigger has extra code in it, but it could work.
You can get the behaviour you want by specifying a combination of locking hints within the scope of the transaction BEGIN TRANSACTION SELECT * FROM SampleTable WITH (TABLOCK, HOLDLOCK) WHERE ID = 1 GO DISABLE TRIGGER [dbo].[copyOnDelete] ON SampleTable GO DELETE * FROM SampleTable WHERE ID = 1 GO ENABLE TRIGGER [dbo].[copyOnDelete] ON SampleTable GO COMMIT TRANSACTION Basically what you are saying with the SELECT at the top is: > Please take a table lock on the table 'SampleTable' and hold that lock until the transaction completes. The lock will be a shared lock until you do the DELETE, at which time it will promote to an exclusive lock. If you want it to be an exclusive lock straight away, then you can swap the `TABLOCK` for `TABLOCKX`.
@Matt you do not need to do any select TABLOCK hint. As the DISABLE TRIGGER will place a **schema lock** on the table inside transaction and all other transactions will wait until the schema lock is released. As can be seen in example I put in the answer.
@Pavel Pawlowski - Yep, I always err on the side of caution :)
I ended up using the approach here [
http://stackoverflow.com/questions/4599082/is-tsql-disable-trigger-scoped-to-the-calling-context][1] as I'm running a script, not a stored procedure and so can't use return. This is a problem because I want to rollback transactions as well as commit them. Now I can use context_info to help me achieve this. Here is my final solution to Move a table safely. BEGIN TRAN; GO DISABLE TRIGGER [dbo].[copyOnDelete] ON SampleTable; GO DECLARE @Context_ERROR VARBINARY(128) = CAST('ERROR' AS VARBINARY(128)); DECLARE @ID INT; DECLARE @MSG VARCHAR(100); SET NOCOUNT ON; -- ENTER ID HERE TO MOVE TO SAVE TABLE. SET @ID = 00000123; -- ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ /*** MOVE SampleTable **______________________________________________**/ IF EXISTS (SELECT 1 FROM SampleTable WHERE ID = @ID) BEGIN SELECT @MSG = 'SampleTable ' + CAST(COUNT(*) AS VARCHAR(20)) + ' row(s) found.' FROM SampleTable WHERE ID = @ID PRINT @MSG; INSERT INTO Save_SampleTable SELECT * FROM SampleTable WHERE ID = @ID; IF (@@ERROR <> 0) SET CONTEXT_INFO @Context_ERROR; -- The below checks if both tables have the same values using union. IF EXISTS (SELECT 1 FROM (SELECT COUNT(*) AS c FROM SampleTable WHERE ID = @ID) AS b INNER JOIN (SELECT COUNT(*) AS UnionCount FROM (SELECT * FROM SampleTable WHERE ID = @ID UNION SELECT * FROM Save_SampleTable WHERE ID = @ID) AS u) s ON s.UnionCount = b.c) BEGIN DELETE FROM SampleTable WHERE ID = @ID; SELECT @MSG = CAST(COUNT(*) AS VARCHAR(20)) + ' row(s) copied to table Save_SampleTable.' FROM Save_SampleTable WHERE ID = @ID PRINT @MSG; IF (@@ERROR <> 0) SET CONTEXT_INFO @Context_ERROR; END ELSE BEGIN SELECT @MSG = CAST(COUNT(*) AS VARCHAR(20)) + ' row(s) from table SampleTable failed to copy.' FROM SampleTable WHERE ID = @ID PRINT @MSG; IF (@@ERROR <> 0) SET CONTEXT_INFO @Context_ERROR; END END ELSE BEGIN PRINT 'SampleTable has nothing to copy.'; END PRINT ''; GO ENABLE TRIGGER [dbo].[copyOnDelete] ON SampleTable GO IF (CONTEXT_INFO() = CAST('ERROR' AS VARBINARY(128))) BEGIN ROLLBACK TRAN; PRINT ':( An error has occured. Rolling back all changes.'; PRINT '__________________________________________________________________'; END ELSE BEGIN COMMIT TRAN; PRINT ':) Deleted successfully.'; PRINT '__________________________________________________________________'; END GO SET CONTEXT_INFO 0x; **Thanks allot everyone for all your help :)** [1]:
http://stackoverflow.com/questions/4599082/is-tsql-disable-trigger-scoped-to-the-calling-context
If that's what works for you, then happy we helped you come to a conclusion. I like the smiley's in the error messages.
