View triggers
A trigger on a view runs automatically when you try to insert, update, or delete data using the view. In SQL Server, you can create only INSTEAD OF triggers on views.
In Database Explorer, you can perform operations on triggers by using the shortcut menu. Right-click a trigger to access options, such as creating, editing, or deleting triggers, or generating scripts for them.
Create a trigger
1. In Database Explorer, right-click the Triggers node or a trigger and select New Trigger.
2. In the CREATE TRIGGER statement template, specify the trigger definition.
3. Click Apply Changes.
Edit a trigger
1. In Database Explorer, right-click a required trigger and select Modify via SQL.
2. In SQL Editor, edit the trigger definition.
3. Click Apply Changes.
Delete a trigger
1. In Database Explorer, right-click the trigger you want to delete and select Delete.
Alternatively, press Delete.
2. Click Yes to confirm the deletion.
Note
You cannot undo the drop trigger operation, as this action permanently deletes its structure. It is recommended that you back up the database before deleting the trigger.
View trigger details
View trigger details in one of these ways:
- In Database Explorer, right-click a required trigger and select Open Editor.
- In Database Explorer, double-click a required trigger.
Turn on a trigger
To turn on a trigger, in Database Explorer, right-click a required trigger and select Enable.
To turn on all triggers, in Database Explorer, right-click the Triggers node and select Enable All.
Note
The Enable option is unavailable if the trigger is already turned on.
Turn off a trigger
To turn off a trigger, in Database Explorer, right-click a required trigger and select Disable.
To turn off all triggers, in Database Explorer, right-click the Triggers node and select Disable All.
Note
The Disable option is unavailable if the trigger is already turned off.
Duplicate a trigger
1. In Database Explorer, right-click a required trigger and select Duplicate Object.
2. In the Duplicate Object dialog, configure the object settings:
- In Destination connection, select the target server.
- In Destination database, select the target database.
- In Destination schema, select the target schema.
- In New object name, specify the object name.
- Select Drop destination object to drop the destination object if it already exists.
- Optional: Select Script Changes, then select To New SQL Window to open the generated script in a new SQL document, or To Clipboard to copy it to the clipboard.
3. Click OK.
Generate a script for a trigger
1. In Database Explorer, right-click a required trigger and select Generate Script As, then select the statement type.
2. Select one of the following output options:
- To New SQL Window – Opens the script in a new SQL document.
- To File – Generates the script to a .sql file.
- To Clipboard – Copies the script to the clipboard.
For more information, see Generate the DDL and DML statements for database objects.
View trigger properties
To view trigger properties, in Database Explorer, right-click the trigger and select Show Details.
Object Viewer opens, displaying detailed information about the trigger.
Refresh the trigger
To update the trigger to reflect the latest changes, in Database Explorer, right-click the trigger or Triggers node and select Refresh, or press F5.
Open the Properties window
To open the Properties window, in Database Explorer, right-click the trigger and select Properties, or press F4.
Filter triggers
1. In Database Explorer, right-click the Triggers node and select Filter.
2. In the Filter Settings (Triggers) dialog, configure the filter parameters.
3. Click OK to apply the filter.
Note
When you open the Triggers node, the number of objects it contains appears in parentheses next to the node name. This number changes based on your filter settings.
Shortcut menu options for a trigger
The following table describes the shortcut menu options available for a trigger in Database Explorer.
| Name | Description |
|---|---|
| New Trigger | Opens a CREATE TRIGGER template in a new SQL document to create a trigger. |
| Modify via SQL | Opens the existing trigger in a new SQL document containing an ALTER TRIGGER statement to update it. |
| Open Editor | Opens the Trigger designer to create a new trigger or modify its code. |
| Enable | Turns on the trigger. The Enable option is unavailable if the trigger is already turned on. |
| Disable | Turns off the trigger. The Disable option is unavailable if the trigger is already turned off. |
| Source Control > Source Control Manager | Opens Source Control Manager, where you can track and manage changes to database schema and static data. When Source Control Manager is already open, this option refreshes it. |
| Source Control > Commit | Opens Source Control Manager with all local changes (if any) selected and ready to commit. When Source Control Manager is already open, this option refreshes it and selects all local database changes. |
| Source Control > Get Latest | Opens Source Control Manager with all remote changes (if any) selected, ready to retrieve using Get Latest. When Source Control Manager is already open, this option refreshes it and selects all remote database changes. |
| Duplicate Object | Opens the Duplicate Object dialog to duplicate the trigger. |
| Generate Script As | Generates a script of the selected DML trigger using the CREATE, CREATE OR ALTER, DROP, DROP and CREATE, and ALTER statements. The script can be saved to a new SQL document or to a file, or copied to the clipboard. |
| Show Details | Opens Object Viewer that displays object details. |
| Delete | Drops the object from the database. Shortcut: Delete |
| Refresh | Updates the object to reflect the latest changes. Shortcut: F5 |
| Properties | Opens the Properties pane that displays the object information. Shortcut: F4 |
Shortcut menu options for the Triggers node
The following table describes the shortcut menu options available for the Triggers node in Database Explorer.
| Name | Description |
|---|---|
| New Trigger | Opens a new SQL document containing the CREATE TRIGGER template to create a trigger. |
| Enable All | Turns on all triggers. |
| Disable All | Turns off all triggers. |
| Source Control > Source Control Manager | Opens Source Control Manager, where you can track and manage changes to database schema and static data. When Source Control Manager is already open, this option refreshes it. |
| Source Control > Commit | Opens Source Control Manager with all local changes (if any) selected and ready to commit. When Source Control Manager is already open, this option refreshes it and selects all local database changes. |
| Source Control > Get Latest | Opens Source Control Manager with all remote changes (if any) selected, ready to retrieve using Get Latest. When Source Control Manager is already open, this option refreshes it and selects all remote database changes. |
| Filter | Opens the Filter Settings dialog to control which triggers should appear in the view in Database Explorer. |
| Show Details | Opens Object Viewer that displays object details. |
| Refresh | Updates the object to reflect the latest changes. Shortcut: F5 |
Bulk operations
In Database Explorer, hold down Ctrl and select multiple triggers. Then right-click the selection and select the required option.
The following table describes the shortcut menu options available for multiple triggers.
| Name | Description |
|---|---|
| Open Editor | Opens the Trigger designers for each selected trigger to recreate or modify them. |
| Enable | Turns on the triggers. |
| Disable | Turns off the triggers. |
| Source Control > Source Control Manager | Opens Source Control Manager, where you can track and manage changes to database schema and static data. When Source Control Manager is already open, this option refreshes it. |
| Source Control > Commit | Opens Source Control Manager with all local changes (if any) selected and ready to commit. When Source Control Manager is already open, this option refreshes it and selects all local database changes. |
| Source Control > Get Latest | Opens Source Control Manager with all remote changes (if any) selected and ready to retrieve using Get Latest. When Source Control Manager is already open, this option refreshes it and selects all remote database changes. |
| Source Control > Link/Unlink Static Data | Opens the Source Control Link Static Data dialog that displays the tables whose data you want to link. |
| Generate Script As | Generates a script of the selected objects using the CREATE, CREATE OR ALTER, DROP, DROP and CREATE, and ALTER statements. The script can be generated to a new SQL document or to a file, or copied to the clipboard. |
| Delete | Drops the selected triggers from the database. Shortcut: Delete |
