Deleting and altering triggers
1. Deleting and altering triggers
You've learned a lot about creating various types of triggers, and had some practice. But what happens when you don't want a trigger anymore? Or when you decide to change the trigger actions? You'll learn how it's done in this lesson.2. Deleting table and view triggers
It's easy to delete a trigger in SQL Server. The syntax is straightforward: DROP TRIGGER followed by the trigger name. This syntax is applicable when you are removing triggers attached to tables or views. In this example, the trigger "PreventNewDiscounts" will be deleted.3. Deleting database triggers
If you want to remove a database-level trigger, you have to make that explicit in the syntax. You will use DROP TRIGGER with the trigger name as you did before, but this time the syntax will be expanded with the ON DATABASE statement. This will inform SQL Server to look at the database level for the trigger to be deleted. In the second example, the trigger "PreventViewsModifications" will be deleted at the database level.4. Deleting server triggers
A slightly different syntax should be used if you want to delete triggers at the server level. As before, you'll use DROP TRIGGER and the trigger name, but then you'll instruct SQL Server to search for the trigger ON ALL SERVER. In this case, the trigger "DisallowLinkedServers" will be deleted from the server.5. Disabling triggers
A deleted trigger can never be used again, unless you recreate the trigger. Deleting triggers is okay when you no longer need them, but what if you just want to avoid them for a brief period? Luckily, SQL Server offers the possibility to disable triggers. A disabled trigger still exists as a SQL Server object, but as long as it's disabled it will not be fired. The syntax to disable a trigger is also straightforward, but note that when disabling you need to specify the object the trigger is attached to, even if it is a table. To switch off a table-level trigger, use DISABLE TRIGGER and the trigger name, and specify the name of the table (for example, "ON Discounts"). Replace that with the ON DATABASE statement for database-level triggers, or the ON ALL SERVER statement for triggers created at the server level.6. Enabling triggers
When you want a disabled trigger to start working again, you simply re-enable it. This is accomplished by using the ENABLE TRIGGER syntax, followed by the trigger name and the scope of the trigger: specify the name of the table or view the trigger is attached to, or use ON DATABASE or ON ALL SERVER. Now all the triggers we disabled previously will be working again.7. Altering triggers
There will be situations where you need to modify a trigger. It might be malfunctioning, or you might want to add new functionality. Whatever the reason, at some point you're likely to end up having to change triggers, either during development or later. In this example, a simple trigger is created on the "Discounts" table to prevent any deletions. You run the code and create the trigger, but you notice you forgot to include the word "remove" between "to" and "data" in the print statement. To fix the error, simply drop the existing trigger, then fix your code and run it again.8. Altering triggers
This can become a hassle in the development phase, when you want to test the trigger and make changes immediately. To avoid this create-and-drop flow, you can use the `ALTER` statement to modify the definition of an existing trigger. Simply replace the CREATE keyword with ALTER, while keeping everything else the same (except for the changes you wish to make, of course!). This example shows how you can add the word "remove" to the message the trigger prints using ALTER.9. Let's practice!
Now it's time to practice deleting and altering triggers!Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.