Database triggers
A database trigger is a database object that automatically runs in response to data definition language (DDL) events, such as creating, modifying, or dropping database objects.
In Database Explorer, you can perform operations on DDL triggers by using the shortcut menu. Right-click a DDL trigger in Database Explorer to access options for specific tasks, such as creating and managing DDL triggers.
Create a DDL trigger
1. In Database Explorer, right-click a trigger or Programmability > Database Triggers node and select New DDL Trigger.
2. Update the CREATE TRIGGER statement template.
3. At the bottom of the SQL document, click Apply Changes.
Edit a DDL trigger
1. In Database Explorer, right-click the DDL trigger you want to edit and select Modify via SQL.
2. Edit the DDL trigger.
3. At the bottom of the SQL document, click Apply Changes.
Alternatively, on the top menu, select File > Save or press Ctrl+S.
Delete a trigger
1. In Database Explorer, right-click the trigger you want to delete and select Delete, or press Delete.
2. Click Yes to confirm the deletion.
Note
You cannot undo the drop trigger operation, as this action permanently deletes the database trigger. To avoid data loss, back up the database before deleting the database trigger.
Turn on a DDL trigger
To turn on a trigger, in Database Explorer, right-click the DDL trigger and select Enable.
To turn on all triggers, right-click the Database Triggers node and select Enable All.
Turn off a DDL trigger
To turn off a trigger, in Database Explorer, right-click the DDL trigger and select Disable.
To turn off all triggers, right-click the Database Triggers node and select Disable All.
View a DDL trigger
To view a DDL trigger, do one of the following:
- In Database Explorer, right-click the trigger and select Open Editor.
- In Database Explorer, double-click the trigger.
Duplicate a DDL trigger
1. In Database Explorer, right-click a required DDL 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 DDL 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 – The script opens in a new SQL document.
- To File – The script is generated to a .sql file.
- To Clipboard – The script is copied to the clipboard.
For more information, see Generate the DDL and DML statements for database objects.
View DDL trigger properties
To view trigger properties, in Database Explorer, right-click the trigger or Database Triggers node and select Show Details.
Object Viewer opens, displaying detailed information about the DDL trigger.
Refresh a DDL trigger
To update the DDL trigger to reflect the latest changes, in Database Explorer, right-click the trigger or Database 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 DDL triggers
You can set which DDL triggers should appear in Database Explorer.
To filter DDL triggers:
1. In Database Explorer, right-click the Database Triggers node and select Filter.
2. In the Filter Settings dialog, configure the filter parameters.
3. Click OK to apply the filter.
Note
When you open the Database 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 specific database trigger
The following table describes the shortcut menu options, which appear when you right-click a specific database trigger in Database Explorer.
| Name | Description |
|---|---|
| New DDL Trigger | Opens a CREATE TRIGGER template in a new SQL document to create a new DDL trigger. |
| Modify via SQL | Opens the existing stored DDL trigger in a new SQL document containing an ALTER TRIGGER statement to update it. |
| Open Editor | Opens the DDL trigger in a new SQL document containing a CREATE TRIGGER statement to create a new trigger or change its code. |
| Enable | Turns on the DDL trigger. |
| Disable | Turns off the DDL trigger. |
| 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 pull 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 DDL 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 to view the object information. Shortcut: F4 |
Shortcut menu options for the Database Triggers node
The following table describes the shortcut menu options, which appear when you right-click the Database Triggers node in Database Explorer.
| Name | Description |
|---|---|
| Enable All | Turns on all triggers. |
| Disable All | Turns off all triggers. |
| New DDL Trigger | Opens a new SQL document containing the CREATE TRIGGER statement template. |
| 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 pull 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 database triggers should appear in Database Explorer. |
| Show Details | Opens Object Viewer that displays object details. |
| Refresh | Updates the object to reflect the latest changes. Shortcut: F5 |
Note
The Source Control shortcut menu is available only after you link the corresponding database to a source-control system.
Bulk operations
In Database Explorer, hold down Ctrl and select multiple database triggers. Then right-click the selection and select the required option.
Note
You can manage multiple triggers in bulk only if they are located on the same server.
The following table describes the shortcut menu options available for multiple database triggers.
| Name | Description |
|---|---|
| Open Editor | Opens the Database Trigger designer for each selected database trigger. |
| Enable | Turns on the selected DDL triggers. |
| Disable | Turns off the selected DDL 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 pull 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 scripts of the selected objects using the CREATE, CREATE OR ALTER, DROP, DROP and CREATE, and ALTER statements. The scripts can be generated to a new SQL document or to a file, or copied to the clipboard. |
| Delete | Drops the selected database triggers from the database. Shortcut: Delete |
