Lock table when trigger was disabled

Question

I want to be able to edit some data in a table, without running the triggers, I can disable and enable triger in my script, but how to ensure that other transactions do not update/insert/delete data in my table until the trigger is enabled again?

begin transaction;

        ;DISABLE TRIGGER my-triger on my_table

--update my_table 
--set some data

-- Can other transaction insert/edit/delete data at this moment with out an active trigger on my_table?

        ;ENABLE TRIGGER my-triger on my_table

commit transaction;

You could take out a table lock on the table. If you want a `TABLOCK` or `TABLOCKX` depends on if you want to allow shared locked or not. This [question](https://stackoverflow.com/q/5102152/2029983) talks about the differences of the two. — Thom A, Aug 14 '23 at 10:32
This seems like a bad idea. Why do you have the triggers in the first place if you are going to sometimes step around them so they can't do what they were intended for in the first place? To me this sounds more like a design problem that you are now finding strange ways to work around. — Sean Lange, Aug 14 '23 at 14:46
I've added new column of DateTime type to existed table and now I need to insert init data to new column. But trigger will rewrite data in this column by current data, if it would be enabled. — VladimirK, Aug 14 '23 at 15:57
@VladimirK - can you elaborate? Presumably you have control over the contents of the trigger, so you can choose what it does/when it does it. For example, you could add another temporary column in the table that says whether the backfill has completed for that row which the trigger would use as a predicate in whatever update/delete statement. Then, once the backfill is done, remove that predicate and drop the temp column. Either way, there are more granular solutions than "lock the table"/"disable trigger for backfill". — Ben Thul, Aug 16 '23 at 14:45

score 0 · Answer 1 · answered Aug 14 '23 at 22:08

Try to be cautious when disabling triggers in a multi-user environment, as doing so could affect other transactions that rely on those triggers.

Based on the code you provided, disabling a trigger does not automatically lock the table. Other transactions could still insert, update, or delete data without activating the trigger. It's either you lock the table explicitly for the duration of your transaction to prevent other transactions from modifying it, or use an isolation level to SERIALIZABLE, to ensure that transactions are executed in such a way that the results are consistent with some order of execution.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;

-- Disable the trigger
ALTER TABLE my_table DISABLE TRIGGER my_trigger;

-- Your update goes here
-- UPDATE my_table SET ...

-- Enable the trigger
ALTER TABLE my_table ENABLE TRIGGER my_trigger;

COMMIT TRANSACTION;

-- Reset the transaction isolation level if needed
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Thank for answer, I've done a lot of experiments, and it is look like if I wrap enable/disable trigger inside explicit begin/commit transaction, other transaction cann't edit data between them, but if I remove explicit begin/commit transaction (and so implicit transaction begin just before "update my_table" and close immediatly after update (before enable triger), they can update data between them, but you may be right, I am not sure, that if I wrap block of code into begin/commit transaction other transaction cann't edit data in my table, but i couldn't confirm this in my experiments — VladimirK, Aug 15 '23 at 07:38

Lock table when trigger was disabled

1 Answers1