[band_id]

)

GO

Everything here should look familiar. These commands are very similar to those used to create the t_bands table. The only difference is the last command that creates the foreign key to the band_id column in the t_bands table.

That’s it for tables. Now try creating the rest of the database on your own. If you run into problems, feel free to use the T-SQL statements that are included on the CD.

Next, we take a quick look at the views, stored procedures, and triggers for SQL Server database objects.

Creating a View

A view is essentially a SQL Server object that specifies exactly how a user will see that data in a database. It is a stored query. Views are useful for enforcing security (that is, granting use access to views, but not tables) and simplifying the user interface to the database by creating views for the most frequently used queries.

You can create views with Enterprise Manager or T-SQL with Query Analyzer. For the remainder of this session, we focus solely on Query Analyzer for the sake of brevity. Generally, if you can create an object using Query Analyzer, using the Enterprise Manager is a cinch.

So, to create a view you use the CREATE VIEW statements as shown in the following example:

CREATE VIEW [owner.]view_name

AS select_statement

In this line, view_name is the name of the view and select_statement is the SQL SELECT statement used to return view results.

Suppose you wanted a view that would return the names of all the bands in the t_bands table. The CREATE VIEW statement would look like this:

USE Music

GO

CREATE VIEW all_bands

AS

SELECT band_title, band_id FROM t_bands

This is a pretty simple example, but a good starting point. To utilize the view, all you need to do is call it from a SQL statement, like SELECT:

SELECT * FROM all_bands ORDER BY band_title

Creating a Stored Procedure

Stored procedures are precompiled T-SQL statements stored in a SQL Server database. Because stored procedures are precompiled, they offer better performance than other types

of queries, including views. Additionally, you can pass parameters to and from stored procedures. To create a stored procedure, you use the CREATE PRODCURE statement, which has the following syntax:

CREATE PROCEDURE procedure_name

[{@parameter_name data_type} [VARYING] [= default] [OUTPUT]] [, ...n]

AS sql_statement

If you wanted to create a simple stored procedure that returns all the albums in your database, ordered alphabetically, you would execute the following statement:

CREATE PROCEDURE pr_albums

AS

SELECT album_title FROM t_albums ORDER BY album_title

This statement creates a stored procedure named pr_albums that returns a list of all the albums in the t_albums table ordered alphabetically. Chances are that if the t_albums table gets fairly large, you wouldn’t want to return all the rows in the table. You might want to return all the albums for a specified band. The following stored procedure, pr_albums2, returns a list of a specified band’s albums, ordered alphabetically:

CREATE PROCEDURE pr_albums2 @iBandID INT

AS

SELECT album_title FROM t_albums

WHERE band_id = @iBandID ORDER BY album_title

This stored procedure accepts a parameter, @iBandID. You then include @iBandID in the SQL statement to return only those rows, or albums titles, whose band_id value is equal to

@iBandID.

Creating a Trigger

A trigger is a special kind of stored procedure that is automatically invoked when the data it is designed to protect is modified. Triggers help to ensure the integrity of data by prohibiting unauthorized or inconsistent changes. For example, with a trigger you could ensure that a band could not be deleted from the t_bands table if that band has an album or albums in the t_t_albums table.

Triggers do not have parameters and cannot be explicitly invoked. They are only fired when you try to insert, update, or delete data from a table. The T-SQL syntax for a trigger is:

CREATE TRIGGER trigger_name

ON table_name

FOR {INSERT | UPDATE | DELETE}

AS sql_statement

Now try to enforce the business rule mentioned earlier. You want to make sure that a band is not deleted if it has an entry in the t_albums table.

Based on the requirements, it would appear that the trigger should be invoked, or fired, whenever a band is being deleted from the t_bands table, right? The syntax for this trigger is:

CREATE TRIGGER trg_DeleteBand ON t_bands

FOR DELETE AS

IF EXISTS(SELECT album_id FROM t_albums, deleted WHERE t_albums.band_id

=

deleted.band_id) BEGIN

RAISERROR(Band has albums!’,16,1)

END

All you are doing is creating a trigger named trg_DeleteBand on the t_bands table. The trigger will be fired whenever a band is being deleted. In order for the band to be deleted, no records can exist in the t_albums table for that band. To validate that no records exist in the t_albums table, you use the IF EXISTS statement, which checks to see if there are any records that match a specified criterion. In your case, the specified criterion is a SQL statement.

REVIEW

In this session, you learned how to create tables, views, stored procedures, and triggers with SQL Server. Tables contain the data in a SQL Server database. A view is essentially a SQL Server object that specifies exactly how a user will see that data in a database. Views are useful for enforcing security and simplifying the user interface to the database (by creating views for the most frequently used queries). Stored procedures and triggers are used to enforce data integrity in a database.

Part II — Saturday Morning

Session 5

Using SQL: A Primer

Session 6

XML: A Primer

Session 7

Developing ASP.NET Pages

Session 8

Using HTML Controls

Session 9

Using Web Controls

Session 10

Introducing User Controls

Part III — Saturday Afternoon

Session 11

Validating User Input

Session 12

Maintaining State in ASP.NET

Session 13

Authentication and Authorization

Session 14

ASP.NET Caching

Session 15

Introducing ADO.NET

Session 16

Navigating the ADO.NET Object Model

Part IV — Saturday Evening

Session 17

Opening a Connection

Session 18

Executing Commands

Session 19

Using DataReaders

Session 20

Introducing Datasets, Part I

P A R T

II

Saturday

Morning

Session 5

Using SQL: A Primer

Session 6

XML: A Primer

Session 7

Developing ASP.NET Pages

Session 8

Using HTML Controls

Session 9

Using Web Controls

Session 10

Introducing User Controls