x

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 :)
more ▼

asked May 17, 2011 at 03:58 AM in Default

DeDogs gravatar image

DeDogs
64 1 1 2

(comments are locked)
10|1200 characters needed characters left

5 answers: sort voted first

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.
more ▼

answered May 17, 2011 at 06:14 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered May 17, 2011 at 04:05 AM

Kev Riley gravatar image

Kev Riley ♦♦
52.8k 47 49 76

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.
May 17, 2011 at 04:18 AM DeDogs
@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.
May 17, 2011 at 04:31 AM WilliamD
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
May 17, 2011 at 04:35 AM Kev Riley ♦♦
@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.
May 17, 2011 at 06:24 AM Kev Riley ♦♦

@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.
May 17, 2011 at 06:37 AM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered May 17, 2011 at 05:03 AM

Kev Riley gravatar image

Kev Riley ♦♦
52.8k 47 49 76

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. :/
May 17, 2011 at 05:13 AM DeDogs
(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered May 17, 2011 at 05:46 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

@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.
May 17, 2011 at 06:15 AM Pavel Pawlowski
@Pavel Pawlowski - Yep, I always err on the side of caution :)
May 17, 2011 at 08:10 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered May 17, 2011 at 10:06 AM

DeDogs gravatar image

DeDogs
64 1 1 2

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.
May 17, 2011 at 01:43 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x117
x84
x52
x38
x3

asked: May 17, 2011 at 03:58 AM

Seen: 3156 times

Last Updated: May 17, 2011 at 03:58 AM