Apply Your Knowledge

Exercises

8.1 Creating an INSERT Trigger

This exercise demonstrates how to create an INSERT trigger on the Sales table in the Pubs database. It will disallow a record with a quantity greater than 500 from being entered into the Sales table of the Pubs database.

Estimated Time: 10 minutes

1. Open the SQL Server Query Analyzer by selecting it from the SQL Server 2000 group under Programs on the Start menu.

2. Type the following code to create the trigger:

    USE Pubs GO CREATE TRIGGER QuantityVerify   ON Sales   FOR INSERT   AS     IF @@ROWCOUNT > 1       BEGIN         --Print warning message         PRINT 'Please only add one record at a time.'         ROLLBACK TRANSACTION       END     ELSE       BEGIN         IF(SELECT qty FROM inserted)> 500         --If the quantity entered is greater than 500           BEGIN             --Print a warning             PRINT 'Cannot Enter Quantity Greater Than 500'             ROLLBACK TRANSACTION           END       END 

3. Now execute the command and then attempt to insert a record that defies the trigger to see the result.

8.2 Creating a DELETE Trigger

This exercise demonstrates how to stop removal of certain records using the DELETE trigger.

Estimated Time: 10 minutes

1. Open the SQL Server Query Analyzer by selecting it from the SQL Server 2000 group under Programs on the Start menu.

2. To create the DELETE trigger, enter the following code. This trigger does not allow removal of stores within California.

    USE Pubs GO CREATE TRIGGER StateCheck   ON Stores   FOR DELETE   AS     DECLARE @counter int     SET @counter = @@ROWCOUNT     DECLARE @state char(2)     DECLARE stores_delete_cursor CURSOR FOR       SELECT state FROM deleted     IF @counter = 1       BEGIN         IF(SELECT state FROM deleted) = 'California'           BEGIN             PRINT 'CANNOT REMOVE STORES IN CALIFORNIA'             --Take back the command             ROLLBACK TRANSACTION           END       END     ELSE       BEGIN         OPEN stores_delete_cursor         FETCH NEXT FROM stores_delete_cursor INTO @state         WHILE           BEGIN             IF @state = 'California'               BEGIN                 PRINT 'CANNOT REMOVE STORES IN CALIFORNIA'                 --Take back the command                 PRINT 'Transaction Cancelled'                 ROLLBACK TRANSACTION                 RETURN                 --Exit trigger               END             FETCH NEXT FROM stores_delete_cursor INTO @state           END         CLOSE stores_delete_cursor       END     DEALLOCATE stores_delete_cursor 

3. Now create a DELETE statement to test this trigger by attempting to delete a record for a store that is in California. Remember to use the WHERE clause to limit the delete.

8.3 Viewing and Deleting a Trigger

This exercise demonstrates how to view trigger information and delete triggers. You must complete Exercise 8.2 before performing this exercise.

Estimated Time: 10 minutes

1. Open the SQL Server Query Analyzer by selecting it from the SQL Server 2000 group under Programs on the Start menu.

2. To view trigger information about the trigger created in Exercise 8.2, StateCheck , execute the following:

    USE Pubs GO sp_help StateCheck GO sp_helptext StateCheck GO 

3. To delete the StateCheck trigger, execute the following:

    DROP TRIGGER StateCheck 

8.4 Managing Data by Using Triggers

This exercise is designed to tie together all the concepts that were dealt with in this chapter. You will create a database with two tables ( Customers and OldCustomers ). You will then create and test a series of triggers to handle inputs and deletions of data in the tables. This exercise will perform all of its work from Query Analyzer.

To make the coding of your triggers easier to read, you are going to eliminate most error handling and support only single record updates. You should not do this when working on your production databases.

Estimated Time: 30 minutes

1. This exercise starts with creating a database and its associated tables. Use the following code:

    USE Master GO CREATE DATABASE TriggerTest   ON     ( Name = 'TrigTestDat',       Filename = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\TrigTest.mdf',       Size = 1MB,       MaxSize = 1MB,       FileGrowth = 0MB)   LOG ON     ( Name = 'TrigTestLog',       Filename = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\TrigTest.ldf',       Size = 1MB,       MaxSize = 1MB,       FileGrowth = 0MB) GO USE TriggerTest GO CREATE TABLE Customers   ( CustID   smallint IDENTITY (1,1),     Name     nvarchar (20),     Balance  money       CONSTRAINT CK_Cust_Balance CHECK (Balance >= 0)) GO 

2. Execute the script and then attempt to insert data into the table using the following commands:

    INSERT INTO Customers (Name, Balance)   VALUES ('Bob', 75.30) INSERT INTO Customers (Name, Balance)   VALUES ('Mary', -35) GO SELECT * FROM Customers 

3. Execute the preceding script. You should notice that there is only one record in the Customers table. You are going to add an AFTER trigger that tests for a negative balance and sets the balance to zero in those cases. Create your trigger with the following code:

    CREATE TRIGGER Neg_Balance   ON Customers   AFTER INSERT   AS     DECLARE @varName nvarchar (20)     DECLARE @varBalance money     SELECT @varName = Name,            @varBalance = Balance       FROM Inserted     IF @varBalance < 0       BEGIN         INSERT INTO Customers (Name, Balance)           VALUES (@varName, 0)       END     ELSE       BEGIN         INSERT INTO Customers (Name, Balance)           VALUES (@varName, @varBalance)         END 

4. Execute the code and then attempt to add the record for Mary from Step 2. You should find that you have the same conflict with the CHECK constraint CK_Cust_Balance as you did in Step 2. That happens because you created an AFTER trigger, and it is executed after the INSERT and after the constraints are checked. Confirm that Neg_Balance is an AFTER trigger by using the following:

    sp_helptext 'Neg_Balance' 

5. Because Neg_Balance is an AFTER trigger, you have to drop the Neg_Balance trigger and re-create it as an INSTEAD OF trigger. Use the following code:

    DROP TRIGGER Neg_Balance GO CREATE TRIGGER Neg_Balance   ON Customers   INSTEAD OF INSERT   AS     DECLARE @varName nvarchar (20)     DECLARE @varBalance money     SELECT @varName = Name,            @varBalance = Balance       FROM Inserted     IF @varBalance < 0       BEGIN         INSERT INTO Customers (Name, Balance)           VALUES (@varName, 0)       END     ELSE       BEGIN         INSERT INTO Customers (Name, Balance)           VALUES (@varName, @varBalance)         END 

6. Execute the code, and attempt to add Mary's record. This time, it should work. Notice that the trigger actually had to issue the INSERT statement again. If the trigger did not issue the insert, then the insert would not have taken place. The second issue of the INSERT (from within the INSTEAD OF trigger) forces the INSTEAD OF trigger to be skipped ; the constraints are checked and any AFTER trigger then fires. Also, note Mary's CustID is set to 4. The two times that the constraints failed were after the insert, and the IDENTITY value was incremented.

   The next piece of code creates an AFTER trigger that lists the users in the table that currently have a $0 balance. This is done after any INSERT , UPDATE , or DELETE . Type and execute the following:

    CREATE TRIGGER Current_Zero_Balance   ON Customers   AFTER INSERT, UPDATE, DELETE   AS     PRINT 'The following customers have a 
     CREATE TRIGGER Current_Zero_Balance ON Customers AFTER INSERT, UPDATE, DELETE AS PRINT 'The following customers have a $0 balance:' SELECT Name, Balance FROM Customers WHERE Balance = 0 GO INSERT INTO Customers (Name, Balance) VALUES ('Bill', -10) GO  
    balance:'     SELECT Name, Balance       FROM Customers       WHERE Balance = 0 GO INSERT INTO Customers (Name, Balance)   VALUES ('Bill', -10) GO 

7. Test the AFTER trigger again, but with a positive balance insert and then an update. Execute the following two statements to test the triggers:

    INSERT INTO Customers (Name, Balance)   VALUES ('Tom', -10) GO UPDATE Customers   SET Balance = 75   WHERE Name = 'Mary' GO 

8. The last major step is to create a DELETE trigger that copies deleted customers to an OldCustomers table. Create the OldCustomers table and the DELETE trigger on the Customers table with the following:

    CREATE TABLE OldCustomers   ( CustID   smallint,     Name     nvarchar (20),     Balance  money) GO CREATE TRIGGER Archive_Deleted_Customer   ON Customers   AFTER DELETE   AS     DECLARE @varCustID   smallint     DECLARE @varName     nvarchar (20)     DECLARE @varBalance  money     SELECT @varCustID = CustID,            @varName = Name,            @varBalance = Balance       FROM Deleted     INSERT INTO OldCustomers (CustID, Name, Balance)       VALUES (@varCustID, @varName, @varBalance)     PRINT 'Customer Archived: ' + @varName GO 

9. To test the final trigger, type and execute the following:

    DELETE FROM Customers   WHERE Name = 'Mary' GO SELECT * FROM OldCustomers 

10. The last statement should see both AFTER DELETE triggers fire on the Customers table. The first one was the Current_Zero_Balance trigger, and the second was the Archive_Deleted_Customer trigger. If you want to change the order of execution, because you have a requirement for reporting purposes, type and execute the following:

     sp_settriggerorder 'Archive_Deleted_Customer', 'FIRST', 'DELETE' GO DELETE FROM Customers   WHERE Name = 'Tom' 

The final output that you should see should now have the triggers executing in reverse order. This exercise should have given you a good overview of all of the topics that were covered in the chapter.

Review Questions

1:	Why would a developer want to use an INSTEAD OF trigger rather than an AFTER trigger?
A1:	When considering the difference in triggers, remember that there can be only one INSTEAD OF trigger per action. This means that the single INSTEAD OF trigger might end up being large and unruly with all the logic that must go into it. Each INSTEAD OF trigger also executes only once per update, so there is no direct recursion. On the plus side of INSTEAD OF triggers, they execute instead of the attempted update. This means that they execute before the data modification and constraints are checked, so they can change the way the data modification is made and avoid constraint violations. INSTEAD OF triggers are also the only types of triggers that can be applied to a view, so that they can modify how updates are performed in a view and allow INSERT , UPDATE , and DELETE support for views that normally would not have allowed these actions. These are two of the most of the most compelling reasons to use an INSTEAD OF trigger. An AFTER trigger is not capable of performing these tasks . In many cases you will implement both types of triggers on a table, each performing the duties that it is best suited for.
2:	What can be done to change the order in which trigger code is executed, and why would you want to change the order of execution?
A2:	You can change the order of trigger code by using INSTEAD OF triggers rather than AFTER triggers. When working with AFTER triggers, you can set a FIRST and LAST trigger by using the sp_settriggerorder stored procedure. You might want to use INSTEAD OF triggers so that the data modifications can be examined and changed before the firing of table constraints. Setting the order of AFTER triggers ( FIRST and LAST ) enables you to have specific actions performed in a certain order. You might have two separate triggers firing on an action: One generates a text file report based on the update, and another attaches the report to an email message. It would be important to have these triggers execute in a specific order.
3:	What is indirect recursion, what is the maximum level of it, and what can be done to prevent it within SQL Server 2000?
A3:	Indirect recursion occurs when a trigger performs an action such as updating a remote table or calling a stored procedure, and that action eventually causes its own table to be modified and it executes again. This is different from direct recursion, where the trigger initiates the action that causes itself to execute again. A DELETE trigger that causes the deletion of similar records in its own table would be an example of direct recursion. To prevent all recursion, you can disable the server option of nested triggers . When nested triggers are enabled, there is a general recursion level of 32 steps that is shared between views, stored procedures, and triggers.
4:	What problems can arise from disabling all recursion and nesting of triggers?
A4:	If you disable the nested triggers server option, then no trigger recursion happens on your server. This can actually cause procedures to produce results that you do not expect. This issue is illustrated in the "Recursive Trigger" section of this chapter.
5:	How are the Inserted and Deleted tables used when a user invokes an UPDATE ?
A5:	When an UPDATE trigger is fired , it sends a copy of the value being modified to the Deleted table and then sends the desired record to the Inserted table. These two tables can then be queried to find out what is being changed during the update.
6:	What steps can be taken to protect trigger code from being reused, and what are the disadvantages of protecting the code?
A6:	You can protect your trigger code by using the WITH ENCRYPTION option when you create the trigger. This prevents anyone (including yourself) from being able to read the trigger definition. The potential problem with this is that you are not able to read the trigger definition. If you have not recorded the trigger elsewhere, you cannot easily recreate or alter it.
7:	What tools can be used to create a trigger, and what are the benefits of each?
A7:	You can create triggers using Query Analyzer, Enterprise Manager, or SQL-DMO. Query Analyzer offers the advantage of being able to execute pre-made script files that might already contain your trigger definitions. Script files offer an excellent way to copy or apply the same triggers to multiple servers. Enterprise Manager has the benefit of being a graphical tool and allows for easier editing or altering of a trigger. SQL-DMO provides another scripting alternative: this time VBScript rather than T-SQL. SQL-DMO can also be used from within a programming language such as Visual Basic or Visual C++. SQL-DMO enables you to add trigger creation features to other applications that you might be creating. Because the SQL-DMO interface will not be focused on for the exam, this chapter only mentioned it as a method in passing.

Exam Questions

1:	You have an accounting SQL Server database application that is accessed by 50 users on your company network. When a user inserts or updates a record, you want to make sure that all the required columns have appropriate values. Which of the following would be best for this situation? A stored procedure and a trigger A batch and a trigger An UPDATE trigger and an INSERT trigger One trigger
A1:	D. A single trigger can be used to perform validation on more than one event, such as an INSERT and an UPDATE . For more information about the differences between trigger types, see the "Overview of Triggers" section of this chapter.
2:	You are a developer for a database. Currently the structure of data and the usage have been causing high CPU utilization on the server. You have decided that you must add several triggers to your database to validate data and generate alerts based on data modifications. You are worried about the impact of the triggers on your already heavily utilized server. Generally, how will triggers affect the performance of your server? Relatively low performance overhead, most of it involved in referencing tables. Severely impacts database performance. Does not impact performance whatsoever. Triggers consume less performance than any other type of resource in SQL Server; execution of programming logic uses most of the performance.
A2:	A. Triggers usually require very little CPU time. Most of the time involved in executing a trigger deals with referencing tables, which might have to be read from the disk. Execution time is usually low, but actual impact can be hampered depending on what is done in the programming code of the trigger. You might code CPU- intensive tasks into your trigger by calling on certain stored procedures or commands. These commands might not be typical in a trigger. For more information about triggers, see the "Triggers" section at the beginning of this chapter.
3:	You have a database that contains several FOREIGN KEY and CHECK constraints. Users are having problems with data entry on the database, because the data that they are adding is constantly in violation of the CHECK constraints. Corporate policy regarding database design prevents you from modifying the current constraints, so you decide to implement your changes via a trigger. Which types of triggers would be best suited for this task? Select the best answer. UPDATE , DELETE , and INSERT triggers. Just UPDATE and INSERT triggers. INSTEAD OF triggers. Triggers cannot be used in this circumstance.
A3:	C. INSTEAD OF triggers would be required for this task, because you must check for constraint violations before the update occurs. If there are constraint violations, then AFTER triggers will not fire. Most likely you will be implementing INSTEAD OF INSERT or INSTEAD OF INSERT, UPDATE triggers. When trigger actions are listed, such as an INSERT trigger, you cannot know for sure whether it is an INSTEAD OF or AFTER trigger, but you should assume that it is a FOR or AFTER trigger if not specifically mentioned. For more information about the order that triggers and constraints are applied, see the "Trigger Actions and Order" section of this chapter.
4:	You are working for a medical agency that tracks statistics for doctors throughout the country; these statistics are later involved in economic decisions. This year, the medical agency plans on creating statistics for the salaries of doctors and storing them in a SalarySurvey table. To get more accurate statistics, the agency does not include values of salaries that are greater than $200,000 or smaller than $10,000. Which of the following is the best way to implement this? All these examples are set to support only single row inserts, which would not be typical on a production database. The following code: CREATE TRIGGER SalaryCheck ON SalarySurvey FOR INSERT AS IF (SELECT MonthlySalary FROM inserted)> 200,000 or (SELECT MonthlySalary FROM inserted)< 10,000 BEGIN RAISERROR ( 'Cannot Enter Salary - out of range', 16, 1) WITH LOG ROLLBACK TRANSACTION END The following code: CREATE TRIGGER SalaryCheck ON SalarySurvey FOR INSERT AS IF (SELECT MonthlySalary FROM updated)> 200,000 or (SELECT MonthlySalary FROM updated)< 10,000 BEGIN RAISERROR ( 'Cannot Enter Salary - out of range', 16, 1) WITH LOG REVERSE TRANSACTION END The following code: CREATE TRIGGER SalaryCheck ON SalarySurvey AFTER UPDATE AS IF (SELECT MonthlySalary FROM inserted)> 200,000 or (SELECT MonthlySalary FROM inserted)< 10,000 BEGIN RAISERROR ( 'Cannot Enter Salary - out of range', 16, 1) WITH LOG ROLLBACK TRANSACTION END The following code: CREATE TRIGGER SalaryCheck ON SalarySurvey FOR INSERT AS IF (SELECT MonthlySalary FROM inserted)> 200,000 or (SELECT MonthlySalary FROM inserted)> 10,000 BEGIN RAISERROR ( 'Cannot Enter Salary - out of range', 16, 1) WITH LOG ROLLBACK TRANSACTION END
A4:	A. This is the only answer choice that is implemented as an INSERT trigger and has correct use of ROLLBACK TRANSACTION . For more information about INSERT , UPDATE , and DELETE triggers, see the "AFTER Triggers" section of this chapter.
5:	In what order do these events happen? Constraints, BEFORE triggers, INSTEAD OF triggers. INSTEAD OF triggers, constraints, BEFORE triggers, AFTER triggers. INSTEAD OF triggers, constraints, AFTER triggers. Constraints, INSTEAD OF triggers, AFTER triggers. None of the above.
A5:	C. The correct order for the events are INSTEAD OF triggers, constraints, AFTER triggers. For more information about triggers and trigger order, see the "Trigger Order" section of this chapter.
6:	Which of the following statements cannot be used within a trigger? Select all that apply. CREATE TABLE ROLLBACK TRANSACTION LOAD TRANSACTION RECONFIGURE
A6:	A, C, D. CREATE TABLE , DROP TABLE , ALTER TABLE , GRANT , REVOKE , DISK , ALTER DATABASE , LOAD DATABASE , RESTORE DATABASE , UPDATE STATISTICS , SELECT INTO , LOAD TRANSACTION , and RECONFIGURE are all commands that cannot be used within a trigger. For more information about creating triggers, see the "Creating Triggers" section of this chapter.
7:	You are a senior developer working at a consulting firm. You have to make sure that a user does not enter an alphabetical character in the Telephone , Date , and SSN columns of the Client table. What is the best way to implement this type of validation? Create an INSERT trigger. Create an INSTEAD OF trigger. Use CHECK constraints. There is no way to implement this.
A7:	C. Making sure a user does not enter data of a different type is not best done with a trigger because the same can be done with a CHECK constraint, which processes faster and with less overhead than a trigger. For more information about different ways to constrain data input, see the "Constraining Data Modifications" section of this chapter.
8:	You plan on implementing a simple triggering strategy for a large college located in the heart of New York. The college has planned a 30-day trip to Paris for those interested. The college enters the names of those desiring to go and then checks to see whether they have paid the fees. If they have not paid the required fees, their names do not get entered in; otherwise , they should be entered. Which type of trigger should the school implement? Indirect recursion trigger (IRT) Direct recursion trigger (DRT) INSERT trigger UPDATE trigger
A8:	C . An INSERT trigger is the best choice because data is validated as it is inserted . Second, there are no such things as direct and indirect recursion triggers; recursion is something the triggers might do. For more information about the types of triggers that exist, see the "Trigger Actions and Order" section of this chapter.
9:	Which of the following statements are not true for all types of triggers? Triggers can fire on all three events UPDATE , DELETE , and INSERT . Only one trigger per table is allowed. Triggers can execute stored procedures. Triggers can be used to validate the data being entered into a table.
A9:	B . More than one trigger may be placed on a table; however, only one INSTEAD OF trigger, per trigger action, may be placed on a table. For more information about restrictions on triggers and trigger functions, see the "Trigger Actions and Order" section of this chapter.
10:	Which of the following methods can be used to create a trigger and then later modify its definition? Sp_createtrigger and sp_altertrigger CREATE TRIGGER and MODIFY TRIGGER CREATE TRIGGER and ALTER TRIGGER Both A and C
A10:	C. The CREATE TRIGGER statement is used to create a trigger, and the ALTER TRIGGER statement is used to later modify the trigger. For more information about the CREATE TRIGGER and ALTER TRIGGER statements, see the "Triggers" section of this chapter.
11:	What should you do before renaming or dropping a trigger? Use sp_freename to ensure the name of your trigger can be reused. Use sp_helpdependants to check for dependent objects. Use sp_depends to examine dependencies. No additional steps are required.
A11:	C. You should check for dependent objects when renaming or deleting a trigger. For more information about checking for dependencies, see the "Renaming Triggers" and "Dropping Triggers" sections of this chapter.
12:	Non-updateable views can be made to accept updates by doing which of the following? Executing sp_makeupdatableview . Creating an AFTER trigger. Creating a DELETE trigger. Creating an INSTEAD OF trigger.
A12:	D. INSTEAD OF triggers can be used to make non-updateable views capable of supporting updates. For more information about functions offered by INSTEAD OF triggers, see the "INSTEAD OF Triggers" section of this chapter.
13:	Which of the following commands would enable you to read a trigger definition? sp_helptext sp_helptrigger sp_displaydef sp_help
A13:	A. The sp_helptext stored procedure can be used to read the trigger definition. For more information about working with trigger definitions, see the "Viewing a Trigger" section of this chapter.

Suggested Readings and Resources

1. Inside SQL Server 2000 , by Kalen Delaney (www.insidesqlserver.com)

   Not a beginner book, but fills in many of the gaps left out of the SQL Server Books Online documentation. Explains fully how SQL Server stores and processes data internally.

2. SQL Server 2000 Books Online

   • Creating and Maintaining Databases: Enforcing Business Rules with Triggers.

   • Accessing and Changing Relational Data: Cursors

3. SQL Server Magazine (www.sqlmag.com)

   The magazine is a great source of current discussions and tips on how to use and implement Microsoft SQL Server. You are able to read current and archived issues, and search across the magazine archives. At the time of writing, a search for "Triggers" yielded 361 hits.