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.
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`.
I ended up using the approach here [
http://stackoverflow.com/questions/4599082/is-tsql-disable-trigger-scoped-to-the-calling-context] 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 :)** :