Chapter 14 - Creating and Managing Triggers

• How to use triggers to enforce data integrity - SQL Server's triggers allow you to enforce very customized referential integrity.

• How to find out information about triggers - SQL Server provides a number of system-stored procedures that can be used to view information on triggers.

• Trigger examples and tips on how to write your own triggers - In this chapter you will see practical examples of how to write triggers that, for example, send e-mail.

Triggers are methods that SQL Server provides to the application programmer and database analyst to ensure data integrity. Triggers are very useful for those databases that are going to be accessed from a multitude of different applications because they enable business rules to be enforced by the database instead of relying on the application software.

Understanding SQL Server Triggers

Triggers have a very low impact on performance to the server and are often used to enhance applications that have to do a lot of cascading operations on other tables and rows.

See Chapter 10, "Managing and Using Indexes and Keys" and Chapter 11, "Managing and Using Rules and Defaults" for more information about DRI.

In SQL Server 6, Microsoft added ANSI compliant DRI statements that can be used in the CREATE TABLE statement. The sorts of rules that can be enforced by them are relatively complex; however, it makes the understanding of the table creation quite difficult.

Besides the inability to perform complex business rule analysis based on values that are supplied when a trigger is executed, DRI has one important limitation: the current implementation does not permit referencing values in other databases. Although this may seem a relatively insignificant problem, it has a substantial impact on those people trying to write distributed applications that may need to check data constraints/values on other databases and servers.

Creating Triggers

CREATE TRIGGER [owner.]trigger_name
ON [owner.]table_name
FOR {INSERT, UPDATE, DELETE}
[WITH ENCRYPTION]
AS sql_statements

• trigger_name—The name of the trigger must conform to standard SQL Server naming conventions.

• INSERT, UPDATE, DELETE—With these keywords, the trigger's scope is defined. This determines which actions will initiate the trigger.

• WITH ENCRYPTION—This option is provided for developers to prevent users in their environment from being able to read the text of the trigger after it has been loaded onto the server. This is very convenient for third party application developers who embed SQL Server into their products and do not want their customers to be able to disassemble the code and modify it.

  SQL Server stores the text of a trigger in the system catalog table syscomments. Use the WITH ENCRYPTION option with care because if the original trigger text is lost, it will not be possible to restore the encrypted text from syscomments.

  
  SQL Server uses the unencrypted text of a trigger stored in syscomments when a database is upgraded to a newer version. If the text is encrypted, it will not be possible for the trigger to be updated and restored into the new database. Make sure that the original text is available to upgrade the database when necessary.
  

  
  To provide a good level of recovery for your applications, you should always maintain an offline copy of your stored procedures, triggers, table definitions and overall structure of the server-side of your SQL Server application. This information can be used to reload the server in case of any problems.
  

• sql_statements—A trigger may contain any number of SQL statements in Transact-SQL, provided they are enclosed in valid BEGIN and END delimiters. Limitations on the SQL permitted in a trigger are described in the next section.

When a trigger is executed, a special table is created by SQL Server into which the data that caused the trigger to execute is placed. The table is either INSERTED for INSERT and UPDATE operations or DELETED for DELETE and UPDATE operations. Because triggers execute after an operation, the rows in the INSERTED table are always a duplicate of one or more records in the trigger's base table. Make sure that a correct join identifies all the characteristics of the record being affected in the trigger table so that data is not accidentally modified by the trigger itself. (See the following examples to get an idea of how to construct a trigger.)

Examining Limitations of Triggers

The following is a list of Transact-SQL statements that are not permitted to be in the body text of a trigger. SQL Server will reject the compilation and storing of a trigger with these statements:

• All database and object creation statements: CREATE DATABASE, TABLE, INDEX, PROCEDURE, DEFAULT, RULE, TRIGGER, and VIEW

• All DROP statements

• Database object modification statements: ALTER TABLE and ALTER DATABASE

• TRUNCATE TABLE

  
  DELETE triggers will not be executed when a TRUNCATE operation is initiated on a table. Because the TRUNCATE operation is not logged, there is no chance for the trigger to be run. However, permission to perform a TRUNCATE is limited to the table owner and to sa—and it cannot be transferred.
  

• Object permissions: GRANT and REVOKE

• UPDATE STATISTICS

• RECONfigURE

• Database load operations: LOAD DATABASE and LOAD TRANSACTION

• All physical disk modification statements: DISK...

• Temporary table creation: either implicit through CREATE TABLE or explicit through SELECT INTO

Additionally, the following are limitations that should be clearly understood:

• A trigger may not be created on a view, but only on the base table or tables that the view was created on.

• Any SET operations that change the environment, while valid, are only in effect for the life of the trigger. All values return to their previous states once the trigger has finished execution.

• Manipulating binary large object (BLOB) columns of datatype TEXT or IMAGE, whether logged or not by the database, will not cause a trigger to be executed.

• SELECT operations that return result sets from a trigger are not advised because of the very special handling of result sets that would be required by the client application code (whether in a stored procedure or not). Take care to make sure that all SELECT operations read their values into locally defined variables available in the trigger.

Using Triggers

Triggers are fired or executed whenever a particular event occurs. In the following sections you will see the different events that can cause a trigger to be executed and some idea of what you may want to do on those events.

Using INSERT and UPDATE Triggers

In Listing 14.1, the trigger is executed whenever a record is inserted into the SALES table or when it is modified. If the order date is not during the first 15 days of the month, the record is rejected.

Listing 14.1 14_1.SQL—Sales Trigger Disallowing Specified Records.

Create Trigger Tri_Ins_Sales
On    SALES
For   INSERT, UPDATE
As

/* declare local variables needed */
Declare     @nDayOfMonth      tinyint

/* Find the information about the record inserted */
Select      @nDayOfMonth = DatePart( Day, I.ORD_DATE )
From  SALES S, INSERTED I
Where S.STOR_ID = I.STOR_ID
And   S.ORD_NUM = I.ORD_NUM
And   S.TITLE_ID = I.TITLE_ID

/* Now test rejection criteria and return an error if necessary */
If @nDayOfMonth > 15
Begin
      /* Note: always Rollback first, you can never be sure what
      kind of error processing a client may do that may force locks
      to be held for unnecessary amounts of time */
      ROLLBACK TRAN
      RAISERROR ( 'Orders must be placed before the 15th of
                   the month', 16, 10 )
End
Go

Using DELETE Triggers

In Listing 14.2, the trigger is executed whenever a user attempts to delete a record from the STORES table. If there are sales at that store, then the request is denied.

Listing 14.2 14_2.SQL—Stores Trigger Disallowing Removal of More Than One Store

Create Trigger Tri_Del_Stores
On    STORES
For   DELETE
As

/* First check the number of rows modified and disallow
anybody from deleting more than one store at a time */
If @@RowCount > 1
Begin
      ROLLBACK TRAN
      RAISERROR ( 'You can only delete one store at a time.', 16, 10 )
End

/* declare a temp var to store the store
that is being delete */
Declare     @sStorID char(4)

/* now get the value of the author being nuked */
Select      @sStorID = D.STOR_ID
From  STORES S, DELETED D
Where S.STOR_ID = D.STOR_ID

If exists (Select *
           From   SALES
           Where  STOR_ID = @sStorID )
Begin
      ROLLBACK TRAN
      RAISERROR ( 'This store cannot be deleted because there are
                  still sales valid in the SALES table.', 16, 10 )
End
Go

Using Triggers That Send E-Mail

Triggers can call any of the extended procedures (xp_*) available to the server and any external procedures that you add to the server with the sp_addextendedproc command. In Listing 14.3, the trigger demonstrates sending e-mail when a record is deleted from the underlying AUTHORS table.

Listing 14.3 14_3.SQL—Trigger Sending E-Mail to ChiefPublisher Indicating that Author Deleted From System.

Create Trigger Tri_Del_Authors_Mail
On    AUTHORS
For   DELETE
As

/* declare some variables to store the author's name */
Declare     @sLName varchar(40),
      @sFName varchar(20),
      @sAuthor varchar(60)

/* now get the value of the author being removed */
Select      @sLName = D.AU_LNAME,
      @sFName = D.AU_FNAME
From  AUTHORS A, DELETED D
Where A.AU_ID = D.AU_ID

/* Send mail message */
Select @sAuthor = @sLName + ', ' + @sFName
exec master.dbo.xp_sendmail @recipient = 'ChiefPublisher',
@message = 'deleted ' + @sAuthor
Go

Using Nested Triggers

See the Chapter 16 section entitled Displaying and Setting Server Options for more information on sp_configure and other options.

Triggers become nested when, during execution of one trigger, it modifies another table on which there is another trigger—which is therefore executed.

You can check your nesting level at any time by inspecting the value in @@NestLevel. The value will be between 0 and 16.

SQL Server cannot detect nesting that causes an infinite loop during the creation of a trigger until the situation occurs at execution time. An infinite loop could be caused by having a trigger, TRIGGER_A on TABLE_A, that executes on an update of TABLE_A, causing an update on TABLE_B. TABLE_B has a similar trigger, TRIGGER_B, that is executed on an update and causes an update of TABLE_A. Thus, if a user updates either table, then the two triggers would keep executing each other indefinitely. If SQL Server detects such an occurrence, it shuts down or cancels the trigger.

If a trigger causes an additional modification of the table from which it was executed, it does not cause itself to executed recursively. SQL Server has no support for re-entrant or recursive stored procedures or triggers in the current version.

For example, suppose we have two triggers: one on the Sales table and one on the STORES table. The two triggers are defined in Listing 14.4.

Listing 14.4 14_4.SQL—Two Triggers Nested if Delete Occurs on Sales Table

/* First trigger deletes stores if the sales are deleted */
Create  Trigger Tri_Del_Sales
On      SALES
For     DELETE
As

/* Announce the trigger being executed */
Print "Delete trigger on the sales table is executing..."

/* declare a temp var to store the store
that is being deleted */
Declare @sStorID char(4),
        @sMsg    varchar(40)

/* now get the value of the store being deleted */
Select  @sStorID = STOR_ID
From    DELETED           /* DELETED is a fake table created 
                             by SQLServer to hold the values of 
                             records deleted */
Group By STOR_ID

/* Now delete the store record */
Select @sMsg = "Deleting store " + @sStorID

Print @sMsg

Delete       STORES
Where        STOR_ID = @sStorID
Go

/* Second trigger deletes discounts if a store is deleted */
Create  Trigger Tri_Del_Stores
On      STORES
For     DELETE
As

/* Announce the trigger being executed */
Print "Delete trigger on the Stores table is executing..."

/* declare a temp var to store the store
that is being deleted */
Declare @sStorID char(4),
        @sMsg    varchar(40)

/* now get the value of the store being deleted */
Select  @sStorID = sTOR_ID
From    DELETED           /* DELETED is a fake table created 
                             by SQLServer to hold the values of 
                             records deleted */
Group By STOR_ID

If @@rowcount = 0
Begin
        Print "No Rows affected on the stores table"
        Return 
End

/* Now delete the store record */
Select @sMsg = "Deleting discounts for store " + @sStorID

Print @sMsg

Delete  DISCOUNTS
Where   STOR_ID = @sStorID
Go

Listing 14.5—Results of Executing Delete on Sales Table

/*-----------------------------
Delete from sales where stor_id = '8042'
-----------------------------*/
Delete trigger on the sales table is executing...
Deleting store 8042
Delete trigger on the Stores table is executing...
Deleting discounts for store 8042

Displaying Trigger Information

Using SQL Enterprise Manager

1. Run SQL Enterprise Manager from the Microsoft SQL Server 6.5 group.

2. Select the Server that you want to work on.

3. Select the table that you want to work on (see fig. 14.1).

   Fig. 14.1 - Once a table is highlighted you can click on the right mouse button and use quick menu to perform common operations that are also available on the Manage menu.

4. From the Manage menu, select Triggers (see fig. 14.2).

   Fig. 14.2 - In the Manage Triggers window of SQL Enterprise Manager, the second combo box on the toolbar lists the triggers that are active on the table in the first combo box.

Using sp_help, sp_depends, and sp_helptext

Using sp_help

sp_help [object_name]

Sp_help is useful to determine who created a trigger and when it was created. Here is an example of the output from sp_help when used on the trigger created below, Tri_Del_Authors:

Listing 14.6 Using sp_help to View Information About Tri_Del_Authors

/*-----------------------------
sp_help Tri_Del_Authors
-----------------------------*/
Name                Owner        Type               When_created
------------------- ------------ ------------------ ---------------
Tri_Del_Authors     dbo          trigger            Nov 26 1995  4:37PM

Data_located_on_segment
-----------------------
not applicable

Listing 14.7 14_5.SQL—Advanced Trigger Demonstrating Information Returned from sp_helptext

/* create a basic trigger to stop anyone deleting an
author that still has records titleauthor table */

Create Trigger Tri_Del_Authors
On    AUTHORS
For   DELETE
As

/* First check the number of rows modified and disallow
anybody from removing more than one author at a time */
If @@RowCount > 1
Begin
      ROLLBACK TRAN
      RAISERROR ( 'You can only delete one author at a time.', 16, 10 )
End

/* declare a temp var to store the author
that is being deleted */
Declare     @nAuID id

/* now get the value of the author being deleted */
Select      @nAuID = D.AU_ID
From  AUTHORS A, DELETED D    /* DELETED is a fake table created
                              by SQL Server to hold the values of
                              records deleted */
Where A.AU_ID = D.AU_ID

If exists (Select      *
           From  TITLEAUTHOR
           Where AU_ID = @nAuID )
Begin
      ROLLBACK TRAN
      RAISERROR ( 'This author cannot be deleted because he/
                  she still has valid titles.', 16, 10 )
End
Go

Using sp_depends

sp_depends object_name

Listing 14.8—Using sp_depends to View Dependency Information on the Authors Table

/*-----------------------------
sp_depends authors
-----------------------------*/
In the current database the specified object is referenced by the following:
name                                     type
---------------------------------------- ----------------
dbo.reptq2                               stored procedure
dbo.titleview                            view
dbo.Tri_Del_Authors                      trigger

Using sp_helptext

The syntax for sp_helptext is as follows:

sp_helptext object_name

I've tried to find out the text for a stored procedure using the sp_helptext system procedure, but the only data that is returned is garbled and unreadable.

The stored procedure was stored in ENCRYPTED format. Contact the creator of the procedure to get the text for the procedure. There is no way to decrypt the data. If you have lost original text and it is critical to return the information, contact Microsoft Technical Support in your region for more information.

Dropping Triggers

To drop a trigger, use the following syntax:

DROP TRIGGER [owner.]trigger_name[,[owner.]trigger_name...]

The following example drops the trigger created above:

Drop Trigger Tri_Del_Authors

From Here...

Look at the following chapter for more information that may be useful in helping you write effective triggers:

• Chapter 10, "Managing and Using Indexes and Keys" discusses how to enforce integrity constraints through table-based declarative referential integrity and unique indexes.

• Chapter 11, "Managing and Using Rules and Defaults" shows a different way to enforce integrity constraints on table columns through rules and bound table defaults.

• Chapter 13, "Managing Stored Procedures and Using Flow-Control Statements" provides information on how to create stored procedures that you can execute as triggers.

Table of Contents

13 - Managing Stored Procedures and Using Flow Control Statements

15 - Creating and Using Cursors