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.
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:
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.
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.
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.
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 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.
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.
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.
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
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 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.
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.
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.
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.
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)
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
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)
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.
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
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.
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
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.
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 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.
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."
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.
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
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.
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)
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 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.
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
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.
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.
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.
SELECT name FROM sysobjects WHERE type = `P'
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)
© Copyright, Macmillan Computer Publishing. All rights reserved.