Re: Disable triggers per transaction 8.2.3

On 10/10/07, Henrik <henke(at)mac(dot)se> wrote:
> Hello list,
>
> I wonder if it is possible to disable triggers for a single transaction.
> I know I can disable triggers per table but then I need to disable
> all triggers in all recursive tables before doing by query.
>
> Can I do:
> BEGIN TRANSACTION;
> DISABLE TRIGGERS;
> DELETE FROM tbl_foo WHERE ID > 5;
> ENABLE TRIGGERS;
> COMMIT;
>
> Or do I have to do:
> BEGIN TRANSACTION;
> ALTER TABLE tbl_foo DISABLE TRIGGERS ALL;
> ALTER TABLE tbl_foo_bar DISABLE TRIGGERS ALL;
>
> DELETE FROM tbl_foo WHERE ID > 5;
>
> ALTER TABLE tbl_foo ENABLE TRIGGERS ALL;
> ALTER TABLE tbl_foo_bar ENABLE TRIGGERS ALL;
>
> COMMIT;
>
> Or is it even possible? I only want my triggers to be disabled for
> the transaction and not the global database.

1. upgrade to 8.2.5 asap
2. disable triggers is possible, but alter acquires an excl lock on
the table. so, while you are disabling for you txn only, nobody else
does anything until you finish (is that what you want?)
3. there are other strategies to attack this problem for particular situations.

merlin