Lock Table, When Disabling, On Delete Triggers

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.

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

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.

One approach has been suggested here http://stackoverflow.com/questions/4599082/is-tsql-disable-trigger-scoped-to-the-calling-context - 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.

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.