In our Project, we’ve created Trigger for generate the transactional log. This log will be updated, whenever new data has been “inserted” or “updated or deleted” the Existing Record by using application or manually by executing T-SQL queries via SQL Client tool.
Recently, we’ve planned to import huge data thru ETL from other databases. In this scenario, trigger will update all the data to transactional log table. So we don’t want keep this record in our transactional log. To avoid this, we planned to disable the trigger in our database.
I followed the given steps to disable/Enable the trigger.
To Find the List of Triggers, you can use the following Query.
SELECT * FROM sys.triggers
The given Query will list all the triggers, which are all existed in your current Databases as well as other information about the triggers.
“sys.triggers” System Table contains a column, Named as “is_disabled”, this column holds the status of the trigger, either enabled or disabled.
“sys.triggers” System Table contains a column, Named as “is_disabled”, this column holds the status of the trigger, either enabled or disabled.
is_disabled = 0 Means Enabled
is_disabled = 1 Means Disabled
There are 2 ways to disable a trigger by following ways
- T-SQL Script
- SQL Server Management studio Dialog
To Disable a Trigger By T-SQL Script:
DISABLE TRIGGER UPDATE_PROGRAMREQUEST_DATA ON PROGRAMREQUEST
After Disabled, if you run this query, you can ensure that you disabled the proper trigger or not.
SELECT * FROM sys.triggers
To Disable a Trigger By SQL Server Management studio (SSMS) Dialog:
- Expand the Table node in SSMS, Which is associated with Trigger
- then select the trigger as shown in the exhibit and select disable
After Trigger has been disabled, trigger icon will change with Red down Arrow, as shown in the figure.
ENABLE TRIGGER UPDATE_PROGRAMREQUEST_DATA ON PROGRAMREQUEST
To Enable a Trigger By SQL Server Management studio Dialog:
- Right click on the disabled trigger, then click “Enable”
Good One
ReplyDelete