Menu
I've search high and low, and I haven't been able to find a satisfactory answer to my question. Which tends to boil down to how exactly a TRANSACTION works in SQL Server.
Basically, will this do what I think it does.
BEGIN TRAN
DISABLE [my_update_trigger] ON [my_table]
/*.. Do Some Updates ..*/
ENABLE [my_update_trigger] ON [my_table]
COMMIT TRAN
I want to be able to fix some data in a table, without running the update triggers
I have on the table. This is for a web app, so I'd like to make sure that if an update is done on the table from the web app, while I'm doing my work, [my_update_trigger] will still fire for the web app.
202
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.
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.
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.
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.
The update stuff is ok - the disable enable etc.
DISABLE TRIGGER [my_update_trigger] ON [my_table]
/*.. Do Some Updates ..*/
ENABLE TRIGGER [my_update_trigger] ON [my_table]
Have a look at the msdn page: http://msdn.microsoft.com/en-us/library/ms189748.aspx
On making it session specific though: I'd doubt if that would work - the disable/enabled are DDL rather than DML, ie they act on the database objects rather than the data. I wouldn't have thought this would be in the scope of a Transaction
167
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
