MS SQL Basics Tutorial: Managing triggers

If you want to show what triggers on the table have to do with the table, you must look at the trigger information. In MS SQL Server, there are several ways to view trigger information. In this section we will describe two commonly used methods, Enterprise Manager and system stored procedures sp_help sp_helptext and sp_depends through the management tools of MS SQL Server.

12.11.1 Use enterprise Manager to display trigger information

Use enterprise Manager to display trigger information in the following steps:

(1) Run Enterprise Manager and log on to the specified server.

(2) Select Database and table. As shown in Figure 12-6.

Figure 12-6 After the table is highlighted, you can also right-click from the pop-up menu to select All Tasks manage trigger complete.

(3) Select all Tasks from the Action menu item and select Manage triggers. As shown in Figure 12-7.

12.11.2 use system stored procedures to view triggers

System stored procedures sp_help sp_helptext and sp_depends provide different information about triggers, respectively.

We will introduce them separately below.

(1) sp_help

The command format for using the sp_help system procedure is:

Sp_help ' Trigger name '

This system procedure allows you to understand the general information of triggers, such as the name, properties, type, and creation

Time.

(2) sp_helptext

The sp_helptext is able to view the body information of the trigger, in the form of the following syntax:

Sp_helptext ' Trigger name '

(3) sp_depends

Sp_depends enables you to view all triggers that are involved in a table referenced by a specified trigger or a specified table, whose

The grammatical forms are as follows:

Sp_depends ' Trigger name '

Sp_depends ' table name '

Note: The user must view the information for the trigger in the current database, and the trigger being viewed must already be created.

12.11.3 Modify a Delete trigger

You can modify the name and body of the trigger by enterprise the Manager and system procedures or the Transaction_sql command.

(1) Use the sp_rename command to modify the name of the trigger

The syntax format is:

Sp_rename Oldname,newname

(2) Procedures for modifying the body of the trigger via enterprise Manager

The procedure for modifying the body of a trigger by Enterprise Manager is the same as viewing the trigger information. Use the Check Syntax option to check the statement after the trigger has been modified.

(3) Modify the body of the trigger through the Alert trigger command

(4) Delete triggers

Users can delete a trigger after they have finished using it, and only the trigger owner has permission to delete the trigger. There are two ways to delete a trigger that you have created:

Use the system command drop TRIGGER to delete the specified trigger, in the form of the following syntax:

DROP TRIGGER Trigger Name

When you delete the table that contains the trigger, MS SQL Server automatically deletes the triggers associated with the table.

Summary of this chapter

This chapter highlights two important concepts in MS SQL Server: Stored procedures and triggers. We point out that a stored procedure, a trigger is a set of SQL statements, and that triggers are, in essence, a special kind of stored procedure. Stored procedures and triggers play an irreplaceable role in the process of database development, such as the maintenance and management of database, and the maintenance of database referential integrity. Therefore, it is essential to have a good understanding of all aspects of stored procedures and triggers, both for developers and for database managers, using stored procedures, especially system stored procedures, in a profound way.

In this chapter, we present a comprehensive and thorough demonstration of the various issues related to stored procedures and triggers through more detailed examples. Specifically, it mainly includes the following aspects: the concepts, roles, and advantages of stored procedures, triggers, methods of creating, deleting, viewing, modifying stored procedures, triggers, stored procedures, application of various complexity of triggers, and several issues to be noted in the process of creating, using stored procedures, and triggers.

See the full set of "MS SQL Basics Tutorials"