Transcript SQL Server Enterprise Manager

IT 390 Business Database Administration
Unit 4 :
Using MS SQL Server
2000 Key Features
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 1
Objectives
•
•
•
•
•
•
•
Review installation options
Review how to create a database
Create and Manage logins with Transact SQL and Enterprise
Manager.
Explain how to create and manage logins.
Apply Key commands and features of Microsoft SQL Database
Server and Transact SQL
Create and manage tables, views, indexes, stored procedures
and triggers.
Describe basic concurrency control features and techniques.
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 2
A Quick Review of a couple of Key Concepts!!
• SQL Server 2000 can be installed on:


Windows 2000 and Windows XP workstations
Windows 2000 Server and Windows Server 2003
• There are two ways to create database, to insert data,
and to modify its structure:


Use the GUI SQL Server Enterprise Manager
Write SQL statements and submit them to SQL Server via
the SQL Query Analyzer utility
• Many SQL Server professionals choose to create
structures via SQL then modify them with the graphical
tools
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 3
View Ridge Gallery
• View Ridge Gallery is a small art gallery that
has been in business for 30 years
• It sells contemporary European and
North American fine art
• View Ridge has one owner,
three salespeople, and two workers
• View Ridge owns all of the art that it sells;
it holds no items on a consignment basis
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 4
Application Requirements
• View Ridge application requirements:






Track customers and their artist interests
Record gallery's purchases
Record customers' art purchases
List the artists and works that have appeared in the
gallery
Report how fast an artist's works have sold and at
what margin
Show current inventory in a Web page
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 5
View Ridge Gallery Database Design
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 6
The IDENTITY Keyword
• SQL Server supports the SQL-92 standard
• The IDENTITY keyword implements a new constraint for
surrogate keys:
 IDENTITY (m, n) creates a surrogate key with an
Identity Seed of m and an Identity Increment of n:
CREATE TABLE CUSTOMER(
CustomerID int
NOT NULL IDENTITY
(1000,1),
Name
char(25)NOT NULL,
CONSTRAINT CustomerPK RIMARY KEY
(CustomerID),
CONSTRAINT CustomerAK1 UNIQUE (Name)
);
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 7
SQL Server Enterprise Manager
Right-Click Databases, then use New Database… to create a new
database
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 8
Creating a Database
Type in the new
database name, and
then click the OK
button.
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 9
SQL Query Analyzer:
Starting SQL Query Analyzer from Enterprise Manager
Use the menu
command
Tools | SQL
Query
Analyzer.
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 10
DDL: Using the CREATE TABLE Command
•You can use the CREATE TABLE command to do
the following:
 Create a new table.
 Create a new table with columns.
• You use the CREATE TABLE command to create
a new table. The syntax for creating a new table
is:
CREATE TABLE <table name>
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 11
Creating a Table Clearly
• The clear syntax to create a new table
is:
CREATE TABLE <table name>
( <column name1> <data type>,
<column name2> <data type>)
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 12
Activity
• Write the SQL statement to create a table named
BookIssue. The column descriptions of the table are:
Field
name
Data type
Description
iIssueID int
Primary key
iBookID int
Foreign key to iBookID
column of the Books table
iMembe int
rID
Foreign key to iMemberID
column of the Members
table
dIssueD DateTime
ate
Not Null
dReturn DateTime
Date
Null
mFine
Null
int
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 13
Solution
CREATE TABLE BookIssue
(iIssueID int PRIMARY KEY,
iBookID int
FOREIGN KEY REFERENCES Books(iBookid),
iMemberID int
FOREIGN KEY REFERENCES
Members(iMemberID),
dIssueDate DateTime NOT NULL,
dReturnDate DateTime NULL,
mFine int NULL)
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 14
SQL Query Analyzer:
Creating the TRANS table in SQL Query Analyzer
Use the
Execute Query
button after the
SQL command
is entered.
Enter the SQL
command in
this window.
Results
appear in this
window.
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 15
SQL Server CREATE TABLE Statements
for the View Ridge Schema
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 16
SQL Server CREATE TABLE Statements
for the View Ridge Schema
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 17
SQL Server CREATE TABLE Statements
for the View Ridge Schema
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 18
SQL Server CREATE TABLE Statements
for the View Ridge Schema
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 19
Class Activity
PQR is a software consultancy group involved in deploying
projects that require back-end storage in a DBMS. The
organization has recently bid on a project involving a large
number of tables and records that requires multiple user
access.
Pedro is responsible for choosing a database to deploy the
project. Which back-end application can he use in this
situation?
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 20
Answer: Logins
•
A login in SQL Server 2000 is a user account with a
predefined set of permissions and privileges on
databases and database objects.
•
For basic logins in SQL Server 2000 you can:

Add a login

Delete a login
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 21
Logins (cont.)
•
The syntax for adding a login is:

sp_addlogin [ @login_name = ]
'login_name'
[ , [ @pass = ] 'pass' ]
[ , [ @default_db = ]
'db_name' ]
[ , [ @default_language = ]
'default_language' ]
[ , [ @sid = ] sid ]
[ , [ @encryptoption = ]
'encrypt_option' ]
-- Remember the ‘@’ symbol is just a placeholder.
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 22
sp_addlogin example for Microsoft
T-SQL
EXEC sp_addlogin ‘jonesa’, ‘ilikecocoa’, ‘northwind’, ‘us_english’
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 23
Logins with SQL Server Enterprise Manager
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 24
Logins with SQL Server Enterprise Manager
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 25
Activity
•
What is the basic syntax (keywords) for dropping and granting
logins using T-SQL?
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 26
Answer
•
What is the basic syntax (keywords) for dropping and granting
logins using T-SQL?
sp_droplogin
sp_grantdbaccess
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 27
Class Activity
XYZ Corp. needs to deploy a large project with a Visual Basic
front end and SQL Server 2000 back end. Polly, the DBA,
wants to grant different types of permissions to different sets of
users. For example, for the group that needs student details,
she wants to give access only to the student database and not
any other database.
What should Polly do in this situation?
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 28
Roles (Polly should use a “role”)
•
Roles allow you to collect several users into a single unit
for which you can apply similar permissions.
•
You can assign a role to a login in a database using the
following syntax:
sp_addrole [ @rolename = ] 'rolename'
[ , [ @ownername = ] 'ownername' ]
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 29
Databases
•
A database is a location in the memory
where you can store your data.
•
Objects of a database are:

Tables

Views

Stored Procedures

Triggers
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 30
Views
• Views are abstraction of a table, similar to a general view of
an object. In a view only select components need to be
retrieved.
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 31
Facts about Views
• Views are nothing but a query block.
• Views get the data from underlying base tables.
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 32
Advantages of Views
• Views helps in applying security policy.
• Views provide data independence to users.
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 33
More about Views
• Views are used to implement column and row level security.
• Views can be encrypted to enable enhanced level security
to tables.
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 34
Activity
• The HR manager asks the DBA to alter the structure of
the aggregates view to add department name. Write a
query to alter the view.
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 35
Solution
ALTER VIEW aggregates
(deptno, dep_name, average, maximum,
minimum, sum, no_sals)
AS
select dep_name, dept_no, avg(sal),
Max(sal), Min(sal), count(sal)
FROM EMPLOYEE
group by dept_no;
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 36
Viewing Tables
in Enterprise Manager
Right-Click
the table
name, then
click
Design
Table to
view table
columns
and
properties.
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 37
Viewing Table Columns and Properties in
Enterprise Manager
Right-click
the white
space and
then click
Properties
to see table
constraints.
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 38
Viewing Table Constraints in Enterprise Manager
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 39
Creating a Relationship Diagram in
Enterprise Manager
Right-click
Diagrams,
then click
New
Database
Diagram to
create a
relationship
sdiagram.
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 40
Table Relationship Diagram
Right-click
a
relationship
line, then
click
Properties
to see the
properties.
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 41
WORK – TRANS Relationship Properties
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 42
Creating Views:
By SQL in SQL Query Analyzer
Do NOT put
a semicolon (;) at
the end of a
CREATE
VIEW
statement in
SQL Query
Analyzer!
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 43
Creating Views:
By GUI in Enterprise Manager
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 44
Indexes
• Database Indexes are conceptually similar to
indexes found in books.
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 45
More about Indexes (Why do we need them?)
• The need for indexes is:
 To enforce integrity constraints.
 For faster retrieval of data from tables.
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 46
Advantages of Indexes
• Index improves the performance of join queries.
• Index increases the performance of query retrieval.
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 47
More about Indexes
• For optimizing a database, every table should have a clustered
index.
• Only one clustered index can be created per table.
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 48
Think about it…
• Is the usage of Indexes appropriate keeping in mind the
database optimization factor?
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 49
Clustered Index
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 50
NonClustered Index
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 51
CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]
INDEX index_name ON table (column [,…n])
[WITH
[PAD_INDEX]
[[,] FILLFACTOR = fillfactor]
[[,] IGNORE_DUP_KEY]
[[,] DROP_EXISTING]
[[,] STATISTICS_NORECOMPUTE]
]
[ON file group]
DROP INDEX USER_DB.STUDENT.DEMO_IDX,
USER_DB.GRADE.DEMO_IDX
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 52
Activity
• The HR manager of Ethnic Blend Inc. asks the
DBA to create index on the vEmployeeID
column of Employee table. Write a query to
create a clustered index on Employee table.
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 53
Solution
CREATE CLUSTERED INDEX emp_indx
ON Employee(vEmployeeID);
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 54
Activity
• The HR manager of Ethnic Blend Inc. asks the
DBA to create a non-clustered index on
Employee table because Employee table is
very frequently updated. Write a query to create
a non-clustered index based on vEmployeeID
on Employee table.
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 55
Solution
CREATE NONCLUSTERED INDEX emp_indx
on Employee(vEmployeeID);
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 56
Stored Procedure
• A stored procedure is a manageable group of
SQL commands that is executed as a single
block. You use the following syntax to create a
stored procedure:
CREATE PROCEDURE <procedure name>
<parameter list>
WITH RECOMPILE or ENCRYPTION
AS <programming/SQL code>
GO
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 57
More about Stored Procedures
• The advantages of stored procedures are:

Memory savings

Application integrity
Database
Stored Procedures
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 58
Stored Procedures
(another advantage is reducing Network traffic)
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 59
Of Course, it IS always possible to get the same result without a
Stored Procedure, but you will use a lot more Network Bandwidth and
Network Resources!
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 60
In SQL, stored procedures are classified into three
categories. They are:
• System-stored procedures
• Catalog-stored procedures
• Additional stored procedures
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 61
Some of the predefined system procedures:
System Procedure
sp_addtype
sp_foreignkeys
sp_helpsql
server online
sp_primarykeys
the table
Used To:
create user-defined data type.
view the foreign key
get the syntax help from sql
help.
return the primary key column of
sp_rename
specified.
rename the user created object.
sp_dropkey
drop a key constraint of a table.
sp_droptype
sp_bindrule
user-defined
sp_unbindrule
usersp_help
database
delete a user-defined data type.
bind a rule to a column or to a
data type.
unbind a rule from a column or a
defined data type.
view information about any
objects.
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 62
sp_addtype
Create user-defined data type.
EXEC sp_addtype SSN, 'VARCHAR(11)', 'NOT NULL'
GO
CREATE TABLE ShowSSNUsage
(EmployeeID INT PRIMARY KEY,
EmployeeSSN SSN, CONSTRAINT CheckSSN
CHECK
( EmployeeSSN LIKE
'[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]' ) )
GO
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 63
More Predefined system procedures for system administration:
System Procedure
Used To:
sp_addgroup
create groups in the database.
sp_dropgroup
drop groups from the database.
sp_addlogin
create a login id to connect to the sql
server.
sp_adduser
create database user account.
sp_droplogin
drop a login id from sql server.
sp_lock
view details of the lock held on the
database objects.
sp_dropuser
drop a database user account.
sp_who
view the details of the processes and users
running on sql server.
sp_configure
view and modify the current settings of sql
server.
sp_defaultdb
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 64
modify the settings for default database for
a login id.
Some of the catalog procedures
Catalog Procedures
sp_column_privileges
Used to:
view the column privilege information of a
particular table.
sp_fkeys
view the foreign key information for the
current database settings, which includes
foreign key references and disabled foreign
keys.
sp_columns
view the information of a particular column of
a table.
sp_pkeys
sp_statistics
view the primary key details of a particular
table.
view all the indexes available for a particular
table.
sp_databases
view all the databases accessible in the sql
server.
sp_tables
view the tables that can be accessed in the
current database.
sp_stored_procedures
view the stored procedures accessible in the
sql server.
sp_datatype_info
view the details of data type available in the
current database.
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 65
Brief Example of a Catalog Procedure
EXEC sp_helpuser
What does this do?
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 66
Additional Stored Procedures
Local-stored procedures
Temporary-stored procedures
Extended-stored procedures
Remote-stored procedures
(The main use of remote stored procedures
is to provide backward compatibility)
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 67
State Whether each Assertion is True or False
Stored procedures are:
a.
Stored inside tables.
b.
Return values.
c.
Are Executed automatically during any database
operation.
d.
Loaded into memory when they are called last.
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 68
Solutions: True or False
a.
b.
c.
d.
TRUE
TRUE
FALSE
FALSE
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 69
Think about it…
• How are stored procedures processed by the
SQL Server?
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 70
Solution (Think about it… )
• Stored procedures are created in the current database.
They are compiled and if no errors are found then are
successfully saved in the database server. When a stored
procedure is executed, an application call is made to the
database server and the stored procedure is loaded into the
server memory. Next time when you again use the same
stored procedure, instead of an application call, the stored
procedure is accessed directly from the memory.
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 71
Class Activity 2
• Identify the system stored procedures from the
following.
a.
sp_addtype
b.
sp_pkeys
c.
sp_columns
d.
sp_column_privileges
e.
sp_foreignkeys
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 72
Solution 2
• The system stored procedures are:

sp_addtype

sp_foreignkeys
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 73
Can you recognize Stored Procedures
from their name?… sp_*
• For Example, the functions of the following system stored
procedures are:

sp_help: Enable you to view information about any
database objects.

sp_defaultdb: Enable you to modify the settings for
default database for a login ID.

sp_dropkey: Enable you to drop a key constraint of a
table.

sp_helpsql : Enable you get the syntax help from SQL
Server online help.
sp_rename: Enable you to rename the user created
object.

© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 74
Class Activity
• What is the function of the following command?
CREATE PROCEDURE sales_list_procedure
WITH RECOMPILE
AS
SELECT * FROM sales
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 75
Solution
• This procedure displays all the records from the
sales table and gets recompiled every time it
gets executed.
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 76
Class Activity
• Create a stored procedure using parameters, which inserts
employee id, first name, last name, designation, and salary
in the employee table.
EmpID
Fname
Lname
Desg
Salary
Available
Senior
Dept_no
0120
Ron
Shin
Sales
Manager
5000
Yes
0121
Nancy
Jones
Team
Leader
3000
Yes
0120
30
0122
Don
Alien
Team
Member
2500
No
0121
30
0123
Jane
Wane
Team
Member
2500
Yes
0124
20
0124
Shelley
Mathew
Team
Leader
3000
Yes
0120
20
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 77
40
Solution
CREATE PROCEDURE emp_insert @empid
number(5), @fname varchar(20),
@lname varchar(20), @desg varchar(20),
@salary number(10)
AS
INSERT employee VALUES
(@empid, @fname, @lname, @desg, @salary)
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 78
Class Activity
• Create a stored procedure using parameters, which lists all
the employees from employee table where the designation
is team leader.
EmpID
Fname
Lname
Desg
Salary
Availab
le
0120
Ron
Shin
Sales
Manager
5000
Yes
0121
Nancy
Jones
Team
Leader
3000
Yes
0120
30
0122
Don
Alien
Team
Member
2500
No
0121
30
0123
Jane
Wane
Team
Member
2500
Yes
0124
20
0124
Shelley
Mathew
Team
Leader
3000
Yes
0120
20
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 79
Senior
Dept_no
40
Solution
CREATE PROCEDURE emp_desg_query@desg
char(10) = 'TEAM LEADER'
AS
SELECT * FROM employee
WHERE desg = @desg
ORDER BY @desg
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 80
Triggers
•Triggers are activated automatically in response
to certain actions, such as data manipulation. A
trigger is executed whenever the table on which it
is created is modified using an INSERT,
UPDATE, or DELETE command.
• The syntax for creating triggers is:
CREATE TRIGGER <trigger name>
ON <table name> <view name>
FOR <INSERT or UPDATE or DELETE>
AS
< BEGIN {SQL commands} END>
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 81
More about Triggers
• Some more uses of triggers are:

Trigger can enforce referential integrity.

Triggers can control updates to a table.

Use of trigger minimize the memory usage.
• Triggers can:
 Be implemented in business rules.
 Be associated with tables only.
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 82
Activity
• Create an INSERT trigger on employee table, which does
not allow the insertion of a duplicate row in the table.
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 83
Solution
CREATE TRIGGER emp_insert_trigger
ON employee
FOR insert
AS
if(select count(*) from employee e,
insert I where e.empid=I.pub_id) = 1
BEGIN
rollback tran
raiseerror ('Duplicate employee id',
12,6)
END
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 84
MORE on Triggers
• An SQL Server trigger is a T/SQL procedure that is
invoked when a specified database activity occurs
• Triggers can be used to:
 Enforce business rules
 Set complex default values
 Update views
 Implement referential integrity actions
• SQL Server only supports INSTEAD OF and AFTER
triggers:
 A table may have one or more AFTER triggers
 AFTER triggers may not be assigned to views
 A view or table may have only one INSTEAD OF
trigger for each triggering action
• Triggers can roll back the transactions that caused them
to be fired
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 85
This is an
AFTER trigger
on INSERT on
the table
TRANS.
It is will set a
default value on
AskingPrice.
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 86
Triggers:
Enforcing a Required Child Constraint
There is an M-M relationship between WORK and
TRANSACTION:
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 87
Triggers:
Enforcing a Required Child Constraint
• The hard way
using two
triggers – this
one enforces
the required
child:
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 88
Triggers:
Enforcing a Required Child Constraint
• The hard way
using two
triggers – this
one deletes any
duplicate
transaction:
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 89
Triggers:
Enforcing a Required Child Constraint
• A better way
using one
trigger – this
one works
with the
Work_Trans
view:
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 90
Concurrency Control
• Three factors determine the concurrency control behavior
of SQL Server:



Transaction isolation level
Cursor concurrency setting
Locking hints provided in the SELECT clause
• Locking behavior also changes, depending on whether
actions occur in the context of transactions or cursors
independently


Therefore, SQL Server places locks on behalf of the
developer
Locks may be placed at many levels of granularity and may
be promoted or demoted as work progresses
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 91
SQL Server Concurrency Options
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 92
Summary
• Creating and Managing logins with Transact SQL and
Enterprise Manager is a key skill
• There are many key commands and features of Microsoft
SQL Database Server and Transact SQL
• Some of these key commands are to create and manage
tables, views, indexes, stored procedures and triggers
• Concurrency control ensures that only one user can
change a data item at any one time in order to maintain
data consistency
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 93
Summary
 Did
you understand the key points from
the Lesson?
 Do
you have any questions?
© 2006 ITT Educational Services Inc.
Course Name: Business Database
Administration Unit 4 Slide 94