Menu
For test correctness of query I need disable all triggers in db. I see that in information_schema exists table TRIGGERS. Is possible temporarily disable all triggers using this table? E.g. like:
update TRIGGERS set TRIGGERS_SCHEMA='myschema_new' where TRIGGERS_SCHEMA='myschema' and after finish all test return all triggers like:
update TRIGGERS set TRIGGERS_SCHEMA='myschema' where TRIGGERS_SCHEMA='myschema_new' May be this can corrupt db or after triggers will not works? I didn't found about it in documentation.
978
You can disable a trigger temporarily using the DISABLE TRIGGER statement. Disable trigger does not delete the trigger. The trigger exists in the current database but it doesn't fire. In the above syntax, trigger_name is the name of the trigger to be disabled under the schema_name schema.
To disable a DML trigger, at a minimum, a user must have ALTER permission on the table or view on which the trigger was created. To disable a DDL trigger with server scope (ON ALL SERVER) or a logon trigger, a user must have CONTROL SERVER permission on the server.
To disable or enable a DML trigger, at a minimum, a user must have ALTER permission on the table or view on which the trigger was created.
You can't disable triggers directly and I wouldn't recommend doing what you're suggesting but you could have your trigger check if a variable (in my example below @disable_triggers) is NULL before executing the trigger's content. For example:
Query:
SET @disable_triggers = 1; // Your update statement goes here. SET @disable_triggers = NULL; Triggers:
IF @disable_triggers IS NULL THEN // Do something use as the trigger isn't disabled. END IF; If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
