Teach Yourself Microsoft SQL Server 6.5 in 21 Days

Previous chapterNext chapterContents


Day 15

Views, Stored Procedures, and Triggers


Congratulations on completing your first two weeks of study in Microsoft SQL Server. Today you are going to learn about views, stored procedures, and triggers. With these three database components, you will be able to hide much of the T-SQL complexities from the users of your databases. The first section discusses the creation and manipulation of views. Views allow you to specify exactly how a user will see data. Views can be thought of as stored queries. The second section covers stored procedures. Stored procedures are precompiled SQL statements. Because stored procedures are precompiled, they run much more quickly than queries do. You will finish Day 15 with a section on triggers. Triggers can be very complex, and they have several rules about how and when they should be created. Triggers allow you to ensure data integrity, domain integrity, and referential integrity within your database. You will also look at each of the three types of triggers--INSERT, UPDATE, and DELETE. As usual, there will be some real world questions and answers provided at the end of this chapter, followed by quiz questions and of course, some exercises to help strengthen your knowledge of what you've learned today.

Creating and Manipulating Views

Views allow you to horizontally and vertically partition information from one or more tables in the database. In other words, with a view you can allow the user to see only selected fields and selected rows. Figure 15.1 shows a view that allows the user to see only the author last name and first name fields (vertical partition), and only authors with a last name that begins with an "M" (horizontal partition).

Figure 15.1. A view created from a horizontally and vertically partitioned table.

Views can also be created to show derived information. For example, you could create a view that would show the author's last name, first name, book title, and then a calculated or derived field showing the number of books sold multiplied by the royalty fee per book.

Views also have the following advantages:

Creating Views

In this section you concentrate on how to create views, view restrictions, and the different types of views available like joins, projections, calculated information, and others.

You can create views using the ISQL/w utility or through the SQL Enterprise Manager.

Here are some examples using the ISQL/w utility:

The syntax for the view statement is:

CREATE VIEW [owner.]view_name [(column_name [, column_name...])]
[WITH ENCRYPTION]
AS select_statement
[WITH CHECK OPTION]

A simple CREATE VIEW statement would look like:

CREATE VIEW all_authors
AS SELECT * FROM authors

You can use this view in a variety of different ways as shown in the following examples.

SELECT * FROM all_authors
SELECT au_fname, au_lname
FROM all_authors
SELECT au_lname
FROM all_authors
WHERE au_lname like `M%'

There are two options that you can specify in the CREATE VIEW statement, WITH CHECK OPTION and WITH ENCRYPTION. By default, data modifications made through a view are not checked to determine whether or not the rows affected will be within the definition of the view. In other words, inserts and updates can be made to the base table even if the view doesn't use them. For example, a view might horizontally partition a table and give you only records that have an author's last name beginning with the letter F. Without using the WITH CHECK OPTION, you could potentially add a new record with a last name of Meredith. The WITH CHECK OPTION forces all data modification statements applied through the view to use the criteria set within the SELECT statement which defines the view. The WITH ENCRYPTION option encrypts the CREATE VIEW statement in the syscomments table. Once a view definition has been encrypted, it cannot be seen by anyone. The only way to decrypt a view is to drop the view and then recreate it.

Here are a couple of sample views created with these two options enabled:

CREATE VIEW myCheck
AS select * from authors
WITH CHECK OPTION
CREATE VIEW myEncrypt
WITH ENCRYPTION
AS select * from authors

When you create views you should always test the SELECT statement before you make it into a view. This will allow you to avoid unexpected results. For example, you could create a view that returns all of the rows in your table (for this example, say that there are only 500 rows in the table). Two years from now, that same table might return 10,000 rows of data. Is this still a good SELECT statement?


TIP: When you create a VIEW, you are creating a database object. You should try to avoid creating broken ownership chains (see Day 7, "User Permissions," for more information). This can be accomplished by having only one developer create all of the objects in your database. This is usually the dbo, or a developer aliased as the dbo. The dbo can then assign permissions to use the view to individual database users and database groups.

There are rules and restrictions you should be aware of when you are creating views.

Gathering Information on Views

To get the text used in the CREATE VIEW statement, you can use the SQL Enterprise Manager or run the sp_helptext stored procedure and pass the view name as a parameter.

sp_helptext myCheck

text
------------------------------------------------------
CREATE VIEW myCheck AS select * from authors WITH CHECK OPTION


NOTE: If a view was created with the ENCRYPTION option set, you cannot use sp_helptext (or the Enterprise Manager) to view the text used to create the view. The view must be dropped and recreated before it can be read.

You can also use the sp_depends stored procedure to get a report of the tables and views on which a view depends, as well as objects that depend on your view. You can run the sp_depends on tables, views, stored procedures, and triggers.

If you applied the sp_depends on your view from above, you would need to do the following:

sp_depends myCheck

In the current database the specified object references the following:
name           type          updated    selected
-----          ----          -------    ---------
dbo.authors    user table    no    	     yes
(1 row(s) affected)

You can also query the following system tables to gather information about a view.

Types of Views

There are different types of views which depend on the type of SELECT statement used to create the view. Take a closer look at projections, joins, aggregates, computed columns, and views based on other views.

Projection

The simplest type of view is called a projection. A projection is simply a subset of columns in a table.

CREATE VIEW my_view
AS SELECT au_lname, au_fname
FROM authors

To run the newly created view:

SELECT * FROM my_view

au_fname    		    au_lname
------------------    --------------
Bennet    	       	    Abraham
Blotchet-Halls    	    Reginald
Carson    		    Cheryl
DeFrance    		    Michel
del Castillo    	    Innes
Dull    		    Ann
Green    		    Marjorie
Greene    		    Morningstar
Gringlesby    		    Burt
Hunter    		    Sheryl
Karsen    		    Livia
Locksley    		    Charlene
MacFeather    		    Stearns
McBadden    		    Heather
O'Leary    		    Michael
Panteley    		    Sylvia
Ringer    		    Albert
Ringer    		    Anne
Smith    		    Meander
Straight    		    Dean
Stringer    		    Dirk
White    		    Johnson
Yokomoto    		    Akiko
(23 row(s) affected)

Joins

Joins link rows from two or more tables by comparing values in the specified columns. For example, you might like to give the user a list of authors from the authors table and the titles of the books they have written from the titles table. These two tables have a many-to-many relationship in the pubs database and therefore use the titleauthor table to create two one-to-many relationships as shown in Figure 15.2.

Figure 15.2. The authors and titles tables are linked by the titleauthor table.

CREATE VIEW authors_titles
AS SELECT authors.au_lname, authors.au_fname, titles.title
FROM authors, titleauthor, titles
WHERE titleauthor.au_id = authors.au_id
AND titles.title_id = titleauthor.title_id

If you then run:

SELECT * FROM authors_titles
WHERE au_lname like `M%'
au_lname    		    au_fname    		title
------------------    ----------------    ------------------------
MacFeather    		    Stearns           		Cooking with Computers:...
MacFeather    		    Stearns    		Computer Phobic AND Non-Phobic...
(2 row(s) affected)

ANALYSIS: This view resulted in a listing showing the last name, first name, and title of any books that authors with a last name beginning with the letter "M" had written.

Other Types of Views

Views can also be created using aggregate functions, computed columns, and views based on other views. Aggregates use aggregate functions like AVG, COUNT and SUM. Computed columns can be used to create summary data for your users. A view from a view can be used to further refine the original view. For example, your original view might give you information on all products with their corresponding prices and the quantities sold. A new view could be created from this view that would compute the quantities by the number of units sold for a particular item number. You can find out more about aggregate functions on Day 11, "Subqueries and Data Correlation."

Here is a computed column view:

CREATE VIEW book_totals AS
SELECT title, (price * ytd_sales) "Total"
FROM titles

You can now use this view:

Select * from book_totals

title    						    total
--------------------------------------------------    --------------
The Busy Executive's Database Guide     		    81,859.05
Cooking with Computers:  Surreptitious Balance...     46,318.20
You Can Combat Computer Stress!     			    55,978.78
Straight Talk About Computers     			    81,859.05
Silicon Valley Gastronomic Treats     			    40,619.68
The Gourmet Microwave     				    66,515.54
The Psychology of Computer Cooking      			    (null)
But Is It User Friendly?    				    201,501.00
Secrets of Silicon Valley     				    81,900.00
Net Etiquette     					    (null)
Computer Phobic AND Non-Phobic Individuals...          	    8,096.25
Is Anger the Enemy?     				    22,392.75
Life Without Fear          					    777.00
Prolonged Data Deprivation: Four Case Studies     	    81,399.28
Emotional Security:  A New Algorithm           		    26,654.64
Onions, Leeks, and Garlic:  Cooking Secrets of ...     7,856.25
Fifty Years in Buckingham Palace Kitchens    		    180,397.20
Sushi, Anyone?       					    61,384.05
(18 row(s) affected)

ANALYSIS: This view returned all of the books in the titles table with a column called Total which contains the price multiplied by the year-to-date sales figures.

Data Modification with Views

You can modify data in a view. Remember that when you modify data in a view, you are modifying data in the underlying tables themselves. There are several rules that apply when you are modifying data through a view.

Removing Views

You can remove views from your database by selecting the view in SQL Enterprise Manager, right-clicking, and choosing Delete from the context menu, or you can use a DROP statement.

To drop a view using code, you use the following syntax.

DROP VIEW [owner.]view_name[,[owner.]view_name...]

To drop a view called myCheck which is owned by the dbo, you would

DROP VIEW dbo.myCheck
This command did not return data, and it did not return any rows

Summary of Views

Views are very powerful and useful objects in your database design. You can give users access to certain columns and rows within your table, but a view is much easier to maintain than user permissions on individual columns and is therefore the preferred method of partitioning information.

Stored Procedures

Stored procedures are precompiled SQL statements that are stored on the SQL Server. Because stored procedures are precompiled, they provide the best performance of any type of query. There are many system-stored procedures defined with an sp_ that gather information from system tables and are especially useful for administration. You can create your own user-defined stored procedures as well. In this section you will look at how stored procedures are created and processed by the system. You will learn how to create stored procedures and gather information about them. You will also learn how to pass parameters to and from stored procedures. Lastly, in this section you will learn how to create stored procedures that will execute automatically when SQL Server starts up.

Stored Procedures; The Sales Pitch

What makes stored procedures so great? What is so special about these SQL Server objects that they get their own section of this book?

Stored procedures are extremely fast database objects that are stored on the SQL Server itself. When you run a stored procedure for the first time, it is run in the following manner.

1. The procedure will be parsed into its component pieces.

2. The components that reference other objects in the database (tables, views, and so on) are checked for their existence. This is also known as resolving.

3. Once resolving is complete, the name of the procedure will be stored in the sysobjects table and the code to create the stored procedure will be saved in syscomments.

4. Compilation continues and, during compilation, a blueprint for how to run the query will be created. This blueprint is often called a normalized plan or a query tree. The query tree will be saved in the sysprocedures table.

5. When the stored procedure is first executed, the query plan will be read and fully compiled into a procedure plan and then run. This saves you the time of reparsing, resolving, and compiling a query tree every time you run the stored procedure.

Another added benefit of using a stored procedure is that once executed, the procedure plan will be stored in the procedure cache. This means that the next time you use that stored procedure, it will be read directly from the cache and run. This gives you a huge performance boost over running a standard SQL query again and again.

That sounds pretty good, but are there any more highlights? Yes there are! They are summarized here.

Creating Stored Procedures

You use the CREATE PROCEDURE statement to create stored procedures. Stored procedures are created in the current database, unless you are creating temporary stored procedures in tempdb. To create a stored procedure you need to have the dbo's permission, be a dbo, or be the SA in the database.


[BBEG]NOTE: If you are creating temporary stored procedures, the name of your procedure must be 20 characters or less and must begin with # for a local temporary stored procedure (only your user session can access this stored procedure) or ## for a global temporary stored procedure (anyone can access your temporary stored procedure). Temporarily stored procedures are created in tempdb.

The following are rules for creating a stored procedure:

Here is the CREATE PROCEDURE statement used to create stored procedures.

CREATE PROCEDURE [owner.]procedure_name 
[;number] [(parameter1 [,parameter2]...[parameter255])] 
[{FOR REPLICATION} | {WITH RECOMPILE}
[{[WITH] | [,]} ENCRYPTION]]
AS sql_statements

Take a look at a couple of sample CREATE PROCEDURE statements and then you'll learn about the ;number, parameter, RECOMPILE, and ENCRYPTION components.

CREATE PROCEDURE spAuthors
AS SELECT au_fname, au_lname
FROM authors
ORDER BY au_lname DESC

This command did not return data, and it did not return any rows

To use this procedure, you can execute it from the ISQL/w window.

EXEC spAuthors

au_fname               au_lname
--------               ---------
Yokomoto    		    Akiko
White    		    Johnson
Stringer    		    Dirk
[. . .]    		    [. . .]
Carson    		    Cheryl
Blotchet-Halls      	    Reginald
Bennet    		    Abraham
(23 row(s) affected)

ANALYSIS: The results are a two column table with the last names and first names shown in descending order.

Gathering Information on Stored Procedures

To get the text used in the CREATE PROCEDURE statement, you can use the SQL Enterprise Manager or run the sp_helptext stored procedure and pass the view name as a parameter.

sp_helptext spAuthors

text
------------------------------------------------------
CREATE PROCEDURE spAuthors AS SELECT au_fname, au_lname
FROM authors ORDER BY au_lname DESC


NOTE: Like a view, if a stored procedure was created with the ENCRYPTION option set, you cannot use sp_helptext (or the Enterprise Manager) to view the text used to create the stored procedure. The procedure must be dropped and recreated before it can be read.

You can also use the sp_depends stored procedure to get a report of the object on which a stored procedure depends.

If you applied the sp_depends to the procedure from above, you would do the following:

sp_depends spAuthors

In the current database the specified object references the following:
name    	    type    	  updated    selected
----    	    ----    	  -------    --------
dbo.authors    user table    no         yes
(1 row(s) affected)

Creating a Group of Procedures

The first option you will look at is the ;number option. By specifying a semicolon and a number, you can create a group of stored procedures. Groups of stored procedures are often created for use in the same application. Maintenance is then easier because all procedures used by a particular application reference the same group. Here is an example of creating a group of procedures.

CREATE PROC group_sp;1
AS SELECT * FROM authors
GO
CREATE PROC group_sp;2
AS SELECT au_lname FROM authors
GO
CREATE PROC group_sp;3
AS SELECT DISTINCT city FROM authors
GO

ANALYSIS: This batch of statements will create a single procedure called group_sp with three different procedures as part of it. To refer to individual procedures, execute them with their ;number as part of the name. For example to get a listing of all of the cities that authors live in, you could:

EXEC group_sp;3

city
----
Ann Arbor
Berkeley
Corvallis
Covelo
Gary
Lawrence
Menlo Park
Nashville
Oakland
Palo Alto
Rockville
Salt Lake City
San Francisco
San Jose
Vacaville
Walnut Creek
(16 row(s) affected)

Once you have created a procedure, you cannot modify it unless you drop the procedure and then recreate it. If you are working with a group of procedures, you cannot drop an individual procedure, the entire group must be dropped and then recreated for a modification to be made.


TIP: When you create stored procedures, test the SQL statements first and then create your stored procedure. This will allow you to avoid unexpected results.

When you drop grouped procedures, you only need to drop the procedure name. Any procedure that is part of that group will also be dropped.

DROP PROCEDUREdbo.group_sp

Using Parameters with Stored Procedures

Parameters allow you to create stored procedures that will behave a little differently every time they are called. For example, you could write a stored procedure that would average a series of test scores passed into it. You don't know what the scores are going be when you create the procedure, but every time the procedure is run, you get your new average. The syntax for the parameter portion of the CREATE PROCEDURE needs a closer look.

@parameter_name datatype [= default|NULL] [OUTPUT]

The @parameter_name specifies the name of the parameter within the procedure. You can declare up to 255 parameters within a single stored procedure. The parameter datatype can be any system-defined or user-defined datatype, except for image. DEFAULT specifies a default value for the parameter.


NOTE: Every parameter in a stored procedure must have a value for the procedure to run. If a parameter has a default value assigned to it, the user does not have to supply a value for that parameter unless he or she wants it to be other than the default. Lastly, if you had a procedure that accepted four parameters and all of them had defaults assigned to them, you could call the procedure and only pass values for the first two parameters. You could not call the procedure and pass a value for the first and third parameter and leave the second parameter blank, if you are passing parameters in order. It is possible to pass parameters in SQL Server by reference which means that you supply the parameter name = parameter. This allows you to pass parameters out of order. When this is the case, you can specify any parameter in any order.

The OUTPUT option allows you to pass information back out of the procedure to the calling procedure. This point is illustrated with some sample code. This stored procedure accepts five parameters, averages them, and then OUTPUT's the average.

CREATE PROCEDUREscores
@score1 smallint,
@score2 smallint,
@score3 smallint,
@score4 smallint,
@score5 smallint,
@myAvg smallint OUTPUT
AS SELECT @myAvg = 
(@score1 + @score2 + @score3 + @score4 + @score5) / 5

To extract the myAvg value from this procedure, you must first declare a variable and then run the procedure.

DECLARE @AvgScore smallint
EXEC scores 10, 9, 8, 8, 10, @AvgScore OUTPUT
SELECT `The Average Score is:  `, @AvgScore
GO
----------------------    --------
The average score is:    9
(1 row(s) affected)

ANALYSIS: Let's review what you just did. You first created the procedure scores with myAvg declared as an OUTPUT variable. You then DECLARED a temporary variable called AvgScore and passed average score into your stored procedure call with the OUTPUT parameter. This placed the value of myAvg from the stored procedure into the AvgScore variable outside the procedure. You then used a SELECT statement to print out the value of AvgScore. Notice that when you passed your values into the stored procedure, you passed them in order by position. This is also known as passing by position. You can also pass by reference. This is done by specifying the parameter name = value when you pass in your variables. When you pass by reference, you can pass your variables in, in any order. Following is some sample code.

DECLARE @AvgScore smallint
EXEC scores
@score1 = 10, @score3 = 9, 
@score2 = 8, @score4 = 8, 
@score5 = 10, @myAvg = @AvgScore OUTPUT
SELECT `The average score is:  `, @AvgScore
GO

You will get the same results as before. Notice that you passed values out of order. Lastly, if you start by passing-by-reference, you must pass-by-reference for the entire procedure call. You cannot switch between pass-by-position and pass-by-reference in the middle of a stored procedure call.

You can also pass information back to the calling procedure using the RETURN keyword. This will pass a variable directly to the calling procedure without the need for the OUTPUT statements needed in both the stored procedure definition and the call to the procedure. Take a look at the code to use the RETURN keyword.

CREATE PROC MyReturn
@t1 smallint, @t2 smallint, @retval smallint
AS SELECT @retval = @t1 + @t2
RETURN @retval

After creating this procedure, you would enter the following to run it:

DECLARE @myReturnValue smallint
EXEC @myReturnValue = MyReturn 9, 9, 0
SELECT `The return value is:  `, @myReturnValue
------------------------    --------
The return value is:    18
(1 row(s) affected)

Using the WITH RECOMPILE options

You can add the WITH RECOMPILE statements in either the CREATE PROCEDURE statements or in the EXEC procedure statements. Their location affects how the stored procedure is processed and run.

CREATE PROCEDURE WITH RECOMPILE

When you use the WITH RECOMPILE statements in the CREATE PROCEDURE, the execution plan will not be saved in the procedure cache. The entire procedure will be recompiled every time it is run. This is similar to the way a standard query is handled. This can be useful in stored procedures with parameters that would make the normal execution plan run poorly. By recompiling every time, the procedure can be optimized for the new parameters. Following is an example of a stored procedure with the WITH RECOMPILE option.

CREATE PROCEDURE MyRecompileProc
WITH RECOMPILE
AS SELECT * FROM authors
ORDER BY au_lname

WITH RECOMPILE Used in the EXEC PROCEDURE Statement

You can also use the WITH RECOMPILE statement in the EXEC PROCEDURE statements. This will compile the stored procedure for that single execution and then store the new plan in the procedure cache for subsequent EXEC PROCEDURE commands. Here is an example of using the WITH RECOMPILE option in an EXEC PROCEDURE statement.

EXEC spAuthors
WITH RECOMPILE
au_fname               au_lname
--------               --------
Yokomoto    		    Akiko
White    		    Johnson
Stringer    		    Dirk
[. . .]    		    [. . .]
Carson    		    Cheryl
Blotchet-Halls    	    Reginald
Bennet    		    Abraham
(23 row(s) affected)

ANALYSIS: The results is a two column table with the last names and first names shown in descending order. The procedure was also recompiled and the new plan was stored in the procedure cache.

Forcing All Stored Procedures to be Recompiled

You can force all stored procedures and triggers that reference a particular table to be recompiled at their next run time by executing the sp_recompile stored procedure.

EXEC sp_recompile authors

Making Your Stored Procedures Run Automatically at SQL Startup

You can have stored procedures autoexecute at the startup of SQL Server. You can have as many autoexec stored procedures as you like, but each separate stored procedure will use up a user connection. You can have one stored procedure call other stored procedures and thus use only one user connection to the server.

The execution of these stored procedures will begin after the last database has been recovered at startup time.

To create these autoexec stored procedures you use the sp_makestartup, sp_unmakestartup, and sp_helpstartup stored procedures.

To bypass the autoexecution of these startup stored procedures, you must start SQL Server with the /T4022 parameter. This will set the trace flag #4022 which bypasses startup stored procedures.

Using the WITH ENCRYPTION Option

The WITH ENCRYPTION option encrypts the SQL statements used to create the procedure and stores the encrypted text in the syscomments table.

Here is an example of using the WITH ENCRYPTION option.

CREATE PROC encrypted_proc
WITH ENCRYPTION
AS SELECT * FROM authors

Of course, just like the WITH ENCRYPTION option used with the CREATE VIEW statements, this one does the same thing. You cannot use sp_helptext or the SQL Enterprise Manager to view the text of the stored procedure.


NOTE: You can implement and run stored procedures on other SQL Servers. This is called a Remote Stored Procedure. To enable the use of Remote Stored Procedures, you follow the following steps.

After this has been set up, you execute the stored procedures in a similar fashion as have locally. The difference is that you must preface the stored procedure like: EXEC servername.dbname.owner.storedprocedure.
For example, if you wanted to run the system stored procedure sp_addlogin on the Accounting server, you would run the following code:

	   EXEC: Accounting.master.dbo.sp_addlogin Muriel

This would add the Muriel login ID to the Accounting SQL server.


Stored Procedure Summary

Stored procedures are a very powerful database component. System stored procedures are useful for database administration and maintenance. User-defined stored procedures are useful for whatever purpose you have designed them. They have advantages over views and queries in that they are precompiled and, after their first execution, their execution plan is stored in the procedure cache that resides in RAM. Another benefit of stored procedures is that you can assign permissions to a user to run a stored procedure even if that user does not have permissions on the underlying tables. You can view some interesting stored procedure code by running an sp_helptext on the stored procedures in the master database, for example: sp_helptext sp_helpdevice.

Working with Triggers

In this section you will learn about a special type of stored procedure called a trigger. Triggers are automatically invoked when you try to modify data that a trigger is designed to protect. Triggers help secure the integrity of your data by preventing unauthorized or inconsistent changes being made. For example, say you have a customers table and an orders table. A trigger can be created that will ensure that when you create a new order, it will have a valid customer id to be attached to. Likewise, you could create the trigger so that if you tried to delete a customer from the customers table, the trigger would check to see if there were any orders still attached to that customer and, if so, halt the delete process.

Triggers do not have parameters and cannot be explicitly invoked. This means that you must attempt a data modification to fire off a trigger. Triggers can also be nested up to sixteen levels. Nested triggers work like this, a trigger on your orders table might add an entry to your accounts receivable table which would, in turn, fire a trigger that checks to see if the customer has any overdue accounts receivable and notifies you if he or she does.

Performance-wise, triggers have a relatively low amount of overhead. Most of the time involved in running a trigger is used up by referencing other tables. The referencing can be fast if the other tables are in memory, or a bit slower if they need to be read from disk.

Triggers are always considered a part of the transaction. If the trigger or any other part of the transaction fails, it is rolled back.

In the past, triggers were the only means of enforcing referential integrity. In SQL Server 6.5, you now have the ability to use DRI or declarative referential integrity which makes most triggers unneccesary. DRI was covered on Day 14, "Data Integrity."

The inserted and deleted Tables

Triggers make use of the inserted and deleted tables. Both of these tables contain the same structure as the base table or the trigger table where the trigger has been created. The inserted and deleted tables reside in RAM as they are logical tables. If you add a new record to the base table, the record will be recorded in the base table itself as well as in the inserted table. Having the values available in the inserted table allows you to access the information without having to create variables to hold the information. When you delete a record, the deleted record is stored in the deleted table. An update is much like a delete followed by an insert. If you update a record, the original will be stored in the deleted table and the modified record will be stored in the base table as well as in the inserted table.

Creating Triggers with the CREATE TRIGGER Statement

A table may have a maximum of three trigger actions defined; INSERT, UPDATE, or DELETE. Each of these actions can be stored in a single trigger or multiple triggers. If stored in different triggers, each trigger name must be unique. For example, you could create a trigger called trInsUpdAuthors on the authors table with a trigger designed for INSERT and UPDATE actions. You could then create an additional trigger called trDelAuthors with the DELETE action defined. If you want to modify the trInsUpdAuthors trigger, you must drop the whole trigger and then recreate it. Only the table owner can create triggers, and this permission cannot be transferred. You create triggers with the CREATE TRIGGER statement. Other rules apply to creating triggers as well:


NOTE: If you modify a trigger, the old trigger will be completely replaced with the new trigger.

If you drop a table with triggers on it, the triggers will automatically be dropped.


Now that all of that's out of the way, take a look at the CREATE TRIGGER statement itself.

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

INSERT and UPDATE

Take a look at an example trigger for both inserts to a table and updates.

CREATE TRIGGER trAddAuthor
ON authors
FOR INSERT, UPDATE
AS raiserror ("%d rows have been modified", 0, 1, @@rowcount)
RETURN

This command did not return data, and it did not return any rows

ANALYSIS: You have just created a trigger that will fire off every time you try to add or update a record in the authors table. The trigger will send you a message about how many rows have been modified. Try inserting a new author and see what happens.

INSERT authors
(au_id, au_lname, au_fname, phone, address, city, state, zip, contract)
VALUES
("555-66-7777", "Frog", "Kermit", "800 444-5656", "123 Sesame Street",
"West EastBrooke", "CA", "90221", 0)
1 rows have been modified
(1 row(s) affected)

ANALYSIS: You successfully added your Kermit Frog record to the authors table. The 1 rows have been modified lets you know that your trigger did, indeed, fire off.

DELETE

When using the DELETE action, you should know that this trigger will not fire if a TRUNCATE TABLE statement has been executed. TRUNCATE TABLE deletes all rows from the table.

To test the DELETE action, first add another record to your table that is similar to the first record you created. You are going to change the primary key field only for your Kermit Frog record as follows:

INSERT authors
(au_id, au_lname, au_fname, phone, address, city, state, zip, contract)
VALUES
("444-55-6666", "Frog", "Kermit", "800 444-5656", "123 Sesame Street",
"West EastBrooke", "CA", "90221", 0)
1 rows have been modified
(1 row(s) affected)

ANALYSIS: You successfully added a second Kermit Frog record to the authors table. Note that you changed the au_id field to 444-55-6666.

Now create a DELETE action trigger that will tell you how many rows are going to be deleted when you run this trigger.

CREATE TRIGGER trDelAuthors
ON authors
FOR DELETE AS raiserror
("%d rows are going to be deleted from this table!", 
0, 1, @@rowcount)

This command did not return data, and it did not return any rows

Now delete all records with a first name of Kermit.

DELETE FROM authors
WHERE au_fname = "Kermit"
2 rows are going to be deleted from this table!
(2 row(s) affected)

Enforcing Data Integrity

Triggers can be used to enforce data integrity within your database. In the past, referential integrity was enforced with triggers. With later versions of SQL Server, you can now use referential integrity constraints as shown in Day 14, "Data Integrity." Triggers are still useful, however, to encapsulate business rules and force cascading changes in your database. A cascading change could be created with a trigger, for example. Say that a particular bookstore was no longer in business. You could create a cascading trigger that would remove the store from the stores table and remove all sales associated with that store_id from the sales table. Create this DELETE action cascading trigger.

First create a couple of dummy tables to work with.

sp_dboption pubs, `Select Into', TRUE
go
SELECT * INTO tblStores from pubs..stores
SELECT * INTO tblSales from pubs..sales
CHECKPOINTing database that was changed.
(6 row(s) affected)
(21 row(s) affected)

Now run a quick SELECT statement to see what you have.

SELECT sa.stor_id, st.stor_name
FROM tblStores st, tblSales sa
WHERE st.stor_id = sa.stor_id
stor_id    		    stor_name
-------               ----------
6380    		    Eric the Read Books
6380     		    Eric the Read Books
7066    		    Barnum's
7066    		    Barnum's
7067    		    News & Brews
7067    		    News & Brews
7067    		    News & Brews
[. . .]      		    [. . .]
7131     		    Doc-U-Mat: Quality Laundry and Books
8042    		    Bookbeat
(21 row(s) affected)

Remember the stor_id 7067 as there are 4 of them, and that's what you will be deleting. Next create the trigger on tblSales which will tell you how many sales will be deleted when the associated store from tblStores is deleted.

CREATE TRIGGER trDelSales
ON tblSales
FOR DELETE AS
raiserror("%d rows are going to be deleted from the sales table!"
, 0, 1, @@rowcount)

This command did not return data, and it did not return any rows

Now create the DELETE trigger on tblStores.

CREATE TRIGGER trDelStore
ON tblStores
FOR DELETE AS
DELETE tblSales FROM deleted where deleted.stor_id = tblSales.stor_id

This command did not return data, and it did not return any rows

Finally, go ahead and delete the stor_id 7067, which is the "News & Brews" store.

DELETE FROM tblStores
WHERE tblStores.stor_id = `7067'
4 rows are going to be deleted from the sales table!
(1 row(s) affected)

ANALYSIS: The DELETE trigger on tblStores fired off and deleted all associated records in tblSales. The DELETE trigger in tblSales fired off to deliver the message that 4 rows are being deleted. If you rerun the SELECT statement from above, you will see that News & Brews is gone and that you no longer have 21 rows of data, but 17.

Encapsulating Business Rules

Encapsulating business rules will normally be done with constraints, defaults, datatypes, and rules, but you could also use a trigger. To continue with your earlier examples, you could modify your trigger trDelSales on the tblSales table to run the business rule: Do not allow any stores to be deleted if sales at any of its stores are greater than or equal to 20. Following is the code to implement this business rule.

CREATE TRIGGER trDelSales
ON tblSales
FOR DELETE AS
IF (SELECT COUNT(*) FROM deleted
    WHERE deleted.qty >= 20) > 0
    BEGIN
        PRINT `You cannot delete any of these stores.'
        PRINT `Some stores have more than 20 sales!'
        PRINT `Rolling back your transaction!'
        ROLLBACK TRANSACTION
    END

This command did not return data, and it did not return any rows

Now test this new trigger.

DELETE FROM tblSales
WHERE stor_id = `7066'
You cannot delete any of these stores.
Some stores have more than 20 sales!
Rolling back your transaction!
(2 row(s) affected)

ANALYSIS: There was at least one store with a stor_id of 7066 that had more than 20 sales. To verify, run this SELECT statement.

SELECT stor_id, qty FROM tblSales

stor_id            qty
------- 	        ---
6380    		5
6380    		3
7066    		50
7066    		75
7067    		10
[. . .]    		[. . .]
8042    		10
8042    		25
8042    		30
(21 row(s) affected)

Now run the DELETE statement again using stor_id 6380. As you can see from your listing above, this should delete those stores as neither entry has a qty field of more than 20.

DELETE FROM tblSales
WHERE stor_id = `6380'

 (2 rows(s) affected)

Rerun the SELECT statement and you will see that both stores with an ID of 6380 are gone, and you have 19 rows of data left rather than 21.

Enforcing Referential Integrity

Triggers can also be used to enforce referential integrity. This is their primary purpose in a database. They are especially useful in cascading updates and deletes. Triggers are tested last when data modifications occur. Constraints are checked first on the trigger table. If a constraint is violated, the trigger will never fire.

Here is an example of a trigger that enforces referential integrity. This trigger will ensure that before a sale is added to the sales table, a valid stor_id exists in the stores table.

CREATE TRIGGER trInsUpdSales
ON tblSales
FOR INSERT, UPDATE AS
IF (SELECT COUNT(*) FROM tblStores, inserted
    WHERE tblStores.stor_id = inserted.stor_id) = 0
    BEGIN
        PRINT `The stor_id you have entered does not exist'
        PRINT `in the stores table!'
        ROLLBACK TRANSACTION
   END

ANALYSIS: This trigger will work on any single UPDATE or INSERT to tblSales. It makes sure that you have a valid stor_id in tblStore. If you ran a SELECT INTO though, this trigger may not fire properly. When you have multiple rows to deal with, you should check to make sure that the rowcount of stor_id inserted = the amount of sales you added. The following shows how you would code this trigger to handle multiple rows.

CREATE TRIGGER trInsUpdSales
ON tblSales
FOR INSERT, UPDATE AS
DECLARE @rc int
SELECT @rc = @@rowcount
IF (SELECT COUNT(*) FROM tblStores, inserted
    WHERE tblStores.stor_id = inserted.stor_id) = 0
    BEGIN
        PRINT `The stor_id you have entered does not exist'
        PRINT `in the stores table!'
        ROLLBACK TRANSACTION
   END
IF (SELECT COUNT(*) FROM tblSales, inserted
    WHERE tblSales.stor_id = inserted.stor_id) <> @rc
    BEGIN
        PRINT `Not all sales have a valid stor_id `
        PRINT `in the stores table!'
        ROLLBACK TRANSACTION
    END

Gathering Information on Triggers

As with the other components you have looked at today, you can run the sp_helptext stored procedure to look at the text of a trigger statement. Of course, encrypted triggers will have no syscomments that you can look at. You should not encrypt any objects unless you absolutely have to. When you upgrade your database, those encrypted objects must be dropped and recreated. Unencrypted objects will automatically be upgraded to the newer version.

Trigger Summary

A trigger is a special type of stored procedure that is executed automatically when data is modified in the trigger table. Triggers help secure the integrity of your data by preventing unauthorized or inconsistent changes being made. Triggers can be used to ensure data integrity, referential integrity, and to encapsulate business rules.

From a performance standpoint, triggers have a relatively low amount of overhead. Most of the time involved in running a trigger is used by referencing other tables.

Triggers are always considered a part of the transaction. If the trigger fails, or any other part of the transaction, it is rolled back.

Summary

Today you learned about three powerful database objects; views, stored procedures, and triggers. Views allow you to give users access to certain columns and rows within your table. System-stored procedures are useful for database administration and maintenance. User-defined stored procedures are useful for whatever purpose you have designed them. Stored procedures have the advantage of being precompiled and therefore run much more quickly than views do. A trigger is a special type of stored procedure that is executed automatically when data is modified in the trigger table. Triggers help secure the integrity of your data by preventing unauthorized or inconsistent changes being made.

Q&A

Q What is faster---triggers, views, or stored procedures?

A
Stored procedures and triggers are both faster than views. Stored procedures and triggers are both precompiled SQL statements and therefore run at the same speed and generally have the same amount of overhead. SQL Server's order of operations will run stored procedures and views before they will execute triggers. For efficiency, if you can catch problems with a stored procedure, you won't get down to the trigger level just to have to rollback everything that's been done already.

Q How can I get a list of all stored procedures in my server?


A
You can look at stored procedures database-by-database by running this query:
SELECT name
FROM sysobjects
WHERE type = `P'
Q How can I get a list of all triggers on my server?

A
You can look at triggers on a database-by-database basis if you run this query. It returns each table with its associated triggers.
SELECT name,
`INSERT' = object_name(instrig),
`UPDATE' = object_name(updtrig),
`DELETE' = object_name(deltrig)
FROM sysobjects
WHERE type = `U'
AND (instrig <> 0 OR updtrig <> 0 OR deltrig <> 0)

Workshop

Quiz

1. What do triggers enforce?

2. You can have _____ number of triggers per table.

3. Views can focus on only the data needed, provide security, allow modifications to base tables, and are faster than stored procedures. True or False?

4. True or False: You can update multiple base tables with a view.

5. You can use a stored procedure that returns information from base tables that you do not have permission on. True or False?

6. You can use a view that returns information from base tables that you do not have permission on. True or False?

7. Declared Referential Integrity (DRI), constraints, datatypes, defaults, and rules have eliminated the need for triggers. True or False?

8. When you drop a table, which of the following database objects are also dropped? Views, stored procedures, triggers.

Exercises

1. Create a view that shows which authors have written which books.

2. Create a trigger that will prevent you from deleting an author if they are associated with a book.

3. Create a stored procedure that will show which books are selling in which stores. Accept one parameter, the title of the book, and assume all books if no parameter is passed. (Hint: you will need to join three tables to accomplish this.)


Previous chapterNext chapterContents


Macmillan Computer Publishing USA

© Copyright, Macmillan Computer Publishing. All rights reserved.