Lesson 2: Creating and Managing Triggers | MCSE Training Kit (Exam 70-229): Microsoft SQL Server(TM) 2000 Database Design and Implementation (Pro Certification)

3 4

In this lesson, you will learn a number of methods for creating and managing triggers. Triggers are commonly created and managed by using Query Analyzer or the manage triggers option in Enterprise Manager. A trigger is created by using the CREATE TRIGGER statement. In the trigger creation process, the trigger is applied to a table or a view. After a trigger is created, it is modified by using the ALTER TRIGGER statement. Triggers are renamed and viewed by using system stored procedures or Enterprise Manager. The DROP TRIGGER statement is used to delete a trigger, and the ALTER TABLE statement is used to enable or disable triggers.

After this lesson, you will be able to:

Create triggers.
Rename triggers and modify their contents.
View, drop, and disable triggers.

Estimated Lesson time: 40 minutes

Creating Triggers Using Transact-SQL

You can use the CREATE TRIGGER statement to create a trigger by using Query Analyzer or a command-prompt tool such as osql. When using CREATE TRIGGER, you must specify the trigger name, the table or view upon which the trigger is applied, the class of trigger (INSTEAD OF or AFTER), the event or events that fire the trigger, and the task that you wish the trigger to perform. Optionally, you can specify whether the trigger should be replicated or encrypted. The WITH APPEND clause remains for backward compatibility but shouldn't be used to create triggers for a SQL Server 2000 database.

The main clauses in a CREATE TRIGGER statement can be summarized as follows:

CREATE TABLE trigger_name

ON table_name or view_name

FOR trigger_class and trigger_type(s)

AS Transact-SQL statements

This section discusses the CREATE TABLE, ON, and FOR/AFTER/INSTEAD OF clauses in detail and provides examples of how they are used in a trigger statement. Lesson 3 discusses the Transact-SQL statements appearing after the AS clause. For more details about trigger clauses not shown here, refer to SQL Server Books Online.

The CREATE TRIGGER Clause

Trigger creation begins with the CREATE TRIGGER clause followed by a trigger name. Triggers do not allow specifying the database name as a prefix to the object name. Therefore, select the database with the USE database_name clause and the GO keyword before creating a trigger. GO is specified because CREATE TRIGGER must be the first statement in a query batch.

Permission to create triggers defaults to the table owner. For consistency, consider creating tables, triggers, and other database objects so that dbo is the owner. For example, to create a trigger named Alerter in the BookShopDB database, you can use the following Transact-SQL code:

 USE BookShopDB GO CREATE TRIGGER dbo.alerter

Trigger names must follow the rules for identifiers. For example, if you decide to create a trigger named Alerter for the Employees Table, you must enclose the name in brackets as shown:

 CREATE TRIGGER dbo.[alerter for employees table]

Administering the trigger object, such as deleting it, also requires that you follow the rules for identifiers.

The ON Clause

Triggers must be assigned to a table or view. Use the ON clause to instruct the trigger on to what table or view it should be applied. When a trigger is applied, the table or view is referred to as the trigger table or the trigger view. For consistency, specify the table or view owner after the ON clause. For example, to apply a trigger to the Employees table named Alerter (where both objects—the table and the trigger—are owned by dbo), you can use the following Transact-SQL code:

 CREATE TRIGGER dbo.alerter ON dbo.employees

A trigger is applied only to a single table or view. If you need to apply the same trigger task to another table in the database, create a trigger of a different name that contains the same business logic. Then, apply the new trigger to the other table. The default trigger class, AFTER, can be applied only to a table. The new trigger class, INSTEAD OF, can be applied to either a table or a view.

The FOR, AFTER, and INSTEAD OF Clauses

A trigger event type must be specified when the trigger is created. Valid event types include INSERT, UPDATE, and DELETE. A single trigger can be fired because of one, two, or all three of the events occurring. If you want a trigger to fire on all events, follow the FOR, AFTER, or INSTEAD OF clause with INSERT, UPDATE, and DELETE. The event types can be listed in any order. For example, to make the trigger named Alerter fire on all events, you can use the following Transact-SQL code:

 CREATE TRIGGER dbo.alerter ON dbo.employees FOR INSERT, UPDATE, DELETE

The FOR clause is synonymous with the AFTER clause. Therefore, the previous code example creates an AFTER trigger. To create Alerter as an INSTEAD OF trigger, you can use the following Transact-SQL code:

 CREATE TRIGGER dbo.alerter ON dbo.employees INSTEAD OF INSERT, UPDATE, DELETE

Notice that the FOR clause is replaced with INSTEAD OF.

The AS Clause

The AS clause and the Transact-SQL language following it designates the task that the trigger will perform. The following example shows how to create an Alerter trigger that sends an e-mail to a user named BarryT when an INSERT, UPDATE, or DELETE occurs on the employees table:

 USE BookShopDB GO CREATE TRIGGER dbo.alerter ON dbo.employees AFTER INSERT, UPDATE, DELETE AS  EXEC master..xp_sendmail 'BarryT',   'A record was just inserted, updated or deleted in the Employees table.' GO

This example is kept simple so that you can see clearly how a task is created in a trigger. There are a number of ways to make the task more useful. For example, you could write the task so that the e-mail message details the exact change that occurred. Lesson 3 explores more complex trigger tasks.

Creating a Trigger Using Enterprise Manager

You can create triggers by using the SQL-DMO interface or by using applications such as Enterprise Manager. To create a trigger in Enterprise Manager, you must first select the database. Next, right-click the table or view to which the trigger is applied, point to All Tasks, and then click Manage Triggers. In the text box of the Trigger Properties dialog box, enter the trigger statement. Figure 9.1 shows the Trigger Properties dialog box containing the code from the previous example.

figure 9.1-the general tab of the trigger properties dialog box for a new trigger

Figure 9.1 The General tab of the Trigger Properties dialog box for a new trigger

Trigger Management

Triggers are powerful database objects that run automatically when a table or view is modified. There are a number of database tools and commands used to manage triggers. Triggers can be:

Modified by using the ALTER TRIGGER statement.
Renamed with the sp_rename system stored procedure.
Viewed by querying system tables or by using the sp_helptrigger and sp_helptext system stored procedures.
Deleted by using the DROP TRIGGER statement.
Disabled or enabled by using the DISABLE TRIGGER and ENABLE TRIGGER clauses of the ALTER TABLE statement.

You can also use the Manage Triggers option in Enterprise Manager to modify, view, and delete triggers. The remainder of this lesson provides details about how to perform these management tasks.

Altering and Renaming Triggers

To modify the text in a trigger, you can delete and recreate it. Alternatively, to skip the deletion step, use the ALTER TRIGGER statement. The ALTER TRIGGER statement syntax is similar to the CREATE TRIGGER statement syntax, but ALTER TRIGGER does not remove the trigger from the SysComments and SysObjects system tables. The following example shows how you can modify the Alerter trigger so that it only reports updates to the Employees table:

 ALTER TRIGGER dbo.alerter ON dbo.employees AFTER UPDATE AS  EXEC master..xp_sendmail 'BarryT',   'A record was just updated in the Employees table.' GO

Notice that UPDATE is the only event specified following the AFTER clause, and notice that the text in the e-mail message was changed.

Triggers are modified in Enterprise Manager from the Manage Triggers option. You learned about the Manage Triggers option in the previous section of this lesson. After you display the Trigger Properties dialog box, select the trigger that you wish to modify. Then, change the word CREATE to ALTER. Figure 9.2 shows how to alter the Alerter trigger using Enterprise Manager.

figure 9.2-the general tab of the trigger properties dialog box with the alter syntax shown.

Figure 9.2 The General tab of the Trigger Properties dialog box with the ALTER syntax shown.

Notice that the GO batch command has been removed. In the first released version of SQL Server 2000, the GO batch command causes an error in Enterprise Manager when attempting to modify a trigger.

There might be times that you need to rename a trigger to comply with new naming conventions or because you are implementing more than one trigger on a table. You can rename a trigger by using the sp_rename system stored procedure. The following example shows how to rename the Alerter trigger to EmpAlerter:

 sp_rename @objname = alerter, @newname = empalerter

Notice that dbo was not specified in the rename syntax. Trigger ownership cannot be transferred by using the sp_rename system stored procedure. If you need to change the ownership of a stored procedure, you must use CREATE TRIGGER. If you are nesting a trigger that you rename, make sure that the calling trigger is modified to call the correct trigger name.

Viewing, Dropping, and Disabling Triggers

When a stored procedure is created, its name and other identifying information are stored in the SysObjects system table of the current database. The trigger text is stored in the SysComments system table. The following SELECT statement will show any triggers applied to tables in the BookShopDB database:

 select * from bookshopdb..SysObjects where type = 'tr'

The type column always lists triggers with a value of tr.

Use the sp_helptrigger system stored procedure to show the properties of a trigger. For example, to show the properties of all triggers defined for the Employees table, type the following:

 sp_helptrigger @tabname = employees

You can query the Text column of the SysComments system table to see the contents of the stored procedure. For a more organized display of information, use the sp_helptext system stored procedure. For example, to show the text contained in a trigger named alerter, type the following:

 sp_helptext @objname = alerter

You can also use the Manage Triggers option of Enterprise Manager to view trigger text.

Deleting a trigger removes it from the SysComments and SysObjects system tables. Use the DROP TRIGGER statement to delete one or more triggers from a database. If you drop a trigger table or a trigger view, any triggers assigned to the table or view are also dropped. To delete the Alerter trigger in the BookShopDB database, type the following:

 USE BookShopDB DROP TRIGGER [dbo].[alerter]

As with the CREATE TRIGGER statement, you cannot specify the database name in the DROP TRIGGER statement. Unlike CREATE TRIGGER, DROP TRIGGER does not have to be the first statement in the batch. For this reason, the code example does not include a GO command between the USE and DROP TRIGGER statements.

You might have to disable a trigger (or triggers) on a table if, for example, you are troubleshooting a problem with a database, testing a database modification, or running a procedure that requires a trigger to be disabled on a table. To disable a trigger, use the ALTER TABLE statement. The following code disables the Alerter trigger on the Employees table:

 ALTER TABLE employees DISABLE TRIGGER alerter

To disable all triggers on a table, use the ALL keyword following the DISABLE TRIGGER clause. To enable one or all triggers, change DISABLE to ENABLE in your ALTER TABLE statement.

Exercise 2: Creating and Managing Triggers

In this exercise, you create and test a trigger that displays a message indicating that the trigger fired. You will then manage the trigger by renaming it, modifying its contents, viewing it, disabling it, and then dropping it from the database. The Transact-SQL code appearing below the AS clause is intentionally simplistic, because Lesson 3 explores this aspect of triggers.

NOTE

For the purpose of this exercise, it's fine to display a message when a trigger fires. However, you should design triggers for your databases that do not display result sets and that do not print messages to the screen.

To create simple triggers on the Authors table in the BookShopDB database

Open Query Analyzer and connect to your local server.
In the Editor pane of the Query window, enter and execute the following code:

 USE BookShopDB GO CREATE TRIGGER dbo.insertindicator ON dbo.authors AFTER INSERT AS PRINT 'The insert trigger fired.'

The CREATE TRIGGER statement creates a trigger named InsertIndicator and applies the trigger to the Authors table of the BookShopDB database. When an insert to the Authors table occurs, this trigger fires and prints a message on the Grids tab in the Results pane.

In the Editor pane of the Query window, enter and execute the following code:

 CREATE TRIGGER dbo.updateindicator ON dbo.authors AFTER UPDATE AS PRINT 'The update trigger fired.' GO CREATE TRIGGER dbo.deleteindicator ON dbo.authors AFTER DELETE AS IF @@ROWCOUNT <> 0 PRINT 'The delete trigger fired.'

The CREATE TRIGGER statements create triggers named UpdateIndicator and DeleteIndicator. These triggers are applied to the Authors table in the BookShopDB database. When an UPDATE or DELETE occurs, these triggers print a message on the Grids tab in the Results pane. Notice that the DeleteIndicator trigger tests the @@ROWCOUNT value. If one or more rows are deleted, the message prints.

To test the triggers on the Authors table

In the Editor pane of the Query window, enter and execute the following code:

 INSERT INTO authors (firstname, lastname, yearborn, yeardied) VALUES ('Max', 'Doe', 1962, 'N/A')

A record is inserted into the Authors table, and the InsertIndicator trigger fires and prints the trigger's message to the Messages tab of the Results pane.

In the Editor pane of the Query window, enter and execute the following code:

 UPDATE authors   SET authors.firstname = 'Tucker'  WHERE authors.firstname = 'Max'

A record is updated in the Authors table, and the UpdateIndicator trigger fires and prints the trigger's message to the Messages tab of the Results pane.

In the Editor pane of the Query window, enter and execute the following code:

 DELETE authors where firstname = 'Tucker'

A record is deleted in the Authors table, and the DeleteIndicator trigger fires and prints the trigger's message to the Messages tab of the Results pane.

To rename, modify, and view a trigger

In the Editor pane of the Query window, enter the following stored procedure code:

 sp_rename @objname=insertindicator, @newname=insupdcontrol

The sp_rename system stored procedure renames the InsertIndicator trigger to InsUpdIndicator. Later, you will modify the trigger to fire upon INSERT and UPDATE events.

Execute the query.

The Messages tab of the Results pane cautions you that renaming an object could cause scripts and stored procedures to fail. The message also indicates that the rename operation was successful.

In the Editor pane of the Query window, enter and execute the following stored procedure code:

 sp_helptrigger @tabname = authors

The sp_helptrigger system stored procedure displays a list of triggers applied to the Authors table.

In the Editor pane of the Query window, enter and execute the following code:

 ALTER TRIGGER dbo.insupdcontrol ON dbo.authors INSTEAD OF INSERT, UPDATE AS PRINT 'Inserts and updates are not allowed at this time.'

The trigger modification converts the renamed trigger into an INSTEAD OF trigger that prevents any inserts and updates to the Authors table. You might want to use this type of trigger to temporarily prevent changes to a table. You can disable and enable triggers by using the ALTER TABLE statement.

In the Editor pane of the Query window, enter and execute the following code:

 SET NOCOUNT ON INSERT INTO authors (firstname, lastname, yearborn, yeardied) VALUES ('Max', 'Doe', 1962, 'N/A') SET NOCOUNT OFF

The INSTEAD OF trigger fires and displays a message stating that updates are not allowed at this time. NOCOUNT is enabled so that a row-affected message does not appear in the Messages tab of the Results pane.

Run a SELECT statement against the Authors table to verify that no record was added.
In the Editor pane of the Query window, enter and execute the following stored procedure code:

 sp_helptext @objname=insupdcontrol

The stored procedure displays the contents of the InsUpdControl trigger.

To disable and drop a trigger

In the Editor pane of the Query window, enter and execute the following code:

 ALTER TABLE authors DISABLE TRIGGER insupdcontrol

The ALTER TABLE statement disables the InsUpdControl trigger on the Authors table.

In the Editor pane of the Query window, enter and execute the following code:

 INSERT INTO authors (firstname, lastname, yearborn, yeardied) VALUES ('Max', 'Doe', 1962, 'N/A') SELECT * FROM Authors where Firstname = 'Max'

A record is successfully inserted into the Authors table, and the record appears in the Grids tab of the Results pane.

In the Editor pane of the Query window, enter and execute the following code:

 DROP TRIGGER insupdcontrol, updateindicator, deleteindicator DELETE authors where firstname = 'Max'

The DROP TRIGGER statement deletes all three triggers applied to the Authors table. The DELETE statement removes the record added to the Authors table.

Lesson Summary

In this lesson, you learned how to create a trigger by using the CREATE TRIGGER statement. You must specify the table or view to which the trigger is applied, the event or events that fire the trigger, and any Transact-SQL code that should run when the trigger fires. You have the option of specifying the trigger class, AFTER or INSTEAD OF. AFTER is synonymous with FOR and is the more common trigger to apply to tables. INSTEAD OF can be applied to tables or views. When using CREATE TRIGGER, you must use it as the first statement in a batch. You can also use Enterprise Manager to create triggers.

Triggers are managed using tools and Transact-SQL statements. In Enterprise Manager, you can perform a number of trigger management tasks using the Manage Triggers option. To modify a trigger in Query Analyzer, use ALTER TRIGGER. To rename a trigger, use the sp_rename system stored procedure. To view the contents of a trigger, use the sp_helptext system stored procedure. To view a trigger's properties, use the sp_helptrigger system stored procedure or the OBJECTPROPERTY function. Triggers are deleted using the DROP TRIGGER statement and are enabled or disabled using the ALTER TABLE statement.