Menu
Is it possible to disable a trigger for a batch of commands and then enable it when the batch is done?
I'm sure I could drop the trigger and re-add it but I was wondering if there was another way.
783
In Object Explorer, connect to an instance of Database Engine and then expand that instance. Expand the database that you want, expand Tables, and then expand the table that contains the trigger that you want to disable. Expand Triggers, right-click the trigger to disable, and then click Disable.
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.
Enabling and disabling DML triggers on a table. Navigate to triggers folder at the table level, select the trigger, Right click on trigger and Click on Enable/Disable to Enable or disable the trigger using SSMS.
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.
DISABLE TRIGGER { [ schema_name . ] trigger_name [ ,...n ] | ALL } ON { object_name | DATABASE | ALL SERVER } [ ; ] http://msdn.microsoft.com/en-us/library/ms189748(SQL.90).aspx
followed by the inverse:
ENABLE TRIGGER { [ schema_name . ] trigger_name [ ,...n ] | ALL } ON { object_name | DATABASE | ALL SERVER } [ ; ] http://msdn.microsoft.com/en-us/library/ms182706(SQL.90).aspx
165
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
