Creating Triggers

You can create and manage triggers in SQL Enterprise Manager. Right-click the table for which you want to manage triggers and choose All Tasks, Manage Triggers from the pop-up menu (see Figure 29.1).

Figure 29.1. Managing triggers for the current table in SQL Enterprise Manager.

Figure 29.2 displays the Trigger Properties window that is displayed after you select a table for which you want to manage a trigger. It provides a basic template from which you can code your trigger.

Figure 29.2. The Trigger Properties window.

TIP

The best way to use the Trigger Properties window is to check for a table with existing triggers and retrieve the source code for it. The code can be copied from the Properties window and pasted into Query Analyzer, which is a more powerful editing tool.

Note that if you make changes in the Trigger Properties window and click OK, SQL Enterprise Manager executes an ALTER TRIGGER command. If you copy and paste the source code into SQL Query Analyzer, make sure that you change CREATE to ALTER or add a drop trigger statement before the create statement.

Another alternative for managing triggers is Query Analyzer. With SQL Server 2000, a couple of new tools have been added to the Query Analyzer to facilitate the creation or modification of triggers. The first addition is the Object Browser that allows you to manage database objects, including triggers. Figure 29.3 shows the Edit menu option for triggers in the Object Browser. It also shows the resulting trigger text (shown in the background) that was generated after the Edit option was chosen for an existing trigger on the employee table in the Pubs database.

Figure 29.3. Managing triggers with the Query Analyzer Object Browser.

The other new Query Analyzer feature for managing triggers is a template. Templates are the basic building blocks for commonly added database objects. SQL Server 2000 added this new feature to streamline the creation of common database objects, including triggers. Figure 29.4 displays the Template tab of Query Analyzer and the available templates for trigger creation. The template text for the creation of a basic trigger is displayed in the right pane of Query Analyzer.

Figure 29.4. Query Analyzer Trigger Templates.

The template variables , such as <trigger name >, can be changed manually or replaced using the Replace Template Parameters option available on the Edit menu of Query Analyzer.

After you have your basic trigger template, you can code the trigger with limited restrictions. Almost every Transact-SQL statement that you would use in a SQL batch or stored procedure is also available for use in the trigger code. The commands that cannot be used in a trigger are found in the following list:

• ALTER DATABASE

• CREATE DATABASE

• DISK INIT

• DISK RESIZE

• DROP DATABASE

• LOAD DATABASE / LOAD LOG

• RECONFIGURE

• RESTORE DATABASE / RESTORE LOG