question

DeDogs avatar image
DeDogs 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 :)
triggertabledeletelockingdisabled
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski avatar image
Pavel Pawlowski answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
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.
16 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@DeDogs - you would disable the trigger, then run the delete inside a transaction (stopping anyone from doing something naughty). If that works out, you then commit and immediately re-enable the trigger.
1 Like 1 ·
@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.
1 Like 1 ·
@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.
1 Like 1 ·
Firstly thank you for your time :) A far as i know you can't use transactions when your using GO statements? I need to use GO statements to make the DISABLE TRIGGER statements work? Otherwise I'd love to use transactions :) Cheers.
0 Likes 0 ·
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
0 Likes 0 ·
Show more comments
Kev Riley avatar image
Kev Riley answered
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.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Interesting and good find ;) Unfortunately the system I'm using has updates weekly that modify DB tables and so id prefer not to modify the original software triggers as they then need to be maintained by me every week. :/
0 Likes 0 ·
Matt Whitfield avatar image
Matt Whitfield answered
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`.
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@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.
0 Likes 0 ·
@Pavel Pawlowski - Yep, I always err on the side of caution :)
0 Likes 0 ·
DeDogs avatar image
DeDogs answered
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
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.