Transcript T_SQLx

T-SQL
• Transact-SQL
is
microsoft
implementation of SQL.
• It contains additional programming
constracts
• T-SQL enables you to write programs that
contain SQL statements.
Using Variables
• You can declare variable using
DECLARE statement, followed by the
variable name and the type
DECLARE @name type
For example
DECLARE @MyProductName nvarchar(40)
DECLARE @MyProductId int
Using Variables
• Variables are initially set to null.
• You set a variables’ value using SET statement
SET @MyProductName= ‘Chai’
SET @MyProductID = 7
The following SELECT statement uses these
variables:
Select ProductId, ProductName, UnitPrice
From Products
Where ProductId=@MyProductID OR
ProductName=@MyProductName
Using Conditional Logic
• T-SQL enables you to use conditional logic
operaters in SQL stataments.
• IF-then-ELSE
IF conditon
statement 1
ELSE
statement 2
You can replace a single statement with multiple
statements by placing those statements within BEGIN
and END statements
Using Conditional Logic
If (Select Count(*) from products where unitprice<5)>0
BEGIN
Print ‘The following products have a UnitPrice of less than 5’
Select ProductId, ProductName, UnitPrice
From Products
Where UnitPrice<5
END
Else
BEGIN
Print ‘There are no products that have a UnitPrice of less than 5’
END
Using CASE Statement
The following example uses a select statement to retrieve
the value Massachusetts returned by the case statement:
Declare @State nchar(2)
Set @State =‘MA’
Declare @StateName nvarchar(15)
Select Case @State as State
When ‘CA’ then ‘California’
When ‘MA’ then ‘Massachusetts’
When ‘NY’ then ‘New York’
End
Using CASE Statement
You can store the value retrived by the SELECT statement in a variable
as shown in the example
Declare @State nchar(2)
Set @State =‘MA’
Declare @StateName nvarchar(15)
Select @StateName=
Case @State
When ‘CA’ then ‘California’
When ‘MA’ then ‘Massachusetts’
When ‘NY’ then ‘New York’
End
Print @StateName
Using CASE Statement
You can also compare a column value in a CASE
statement
Select Price=
Case
When UnitPrice is NULL then 'Unknown'
When UnitPrice <10 then 'Less than 10'
When UnitPrice =10 then '10'
Else 'Greater than 10'
End
From Products
While Loops
While conditon
statement
The following example shows a while loop:
Declare @count int
Set @count = 5
While (@count>0)
Begin
Print ‘count=’ + convert(nvarchar,@count)
Set @count=@count-1;
End
Using Waitfor Statement
There are times when you want your program
to pause before running some code to perform
a specific action, such as running a batch
program at night to update customer records
WAITFOR {DELAY ‘ time interval’ | TIME
‘actual time’}
Waitfor Delay ‘00:00:05’ waits for a time
interval of 5 seconds
Using Waitfor Statement
Using WAITFOR TIME: The following example executes
the stored procedure sp_update_job at 10:20 P.M. (22:20).
USE msdb;
EXECUTE sp_add_job @job_name = 'TestJob';
BEGIN
WAITFOR TIME '22:20';
EXECUTE sp_update_job @job_name = 'TestJob',
@new_name = 'UpdatedJob';
END;
GO
Using Waitfor Statement
Using WAITFOR DELAY: The following
example executes the stored procedure after a
two-hour delay.
BEGIN
WAITFOR DELAY '02:00';
EXECUTE sp_helpdb;
END;
GO
Using Cursor
• When you execute a SELECT statement, all
the effected rows are returned in one go.
• Sometimes you might want to take some
action based on the column values retrived
for a particular row.
• To do this, you can use a cursor to process
rows retrived from the database one row at a
time.
Using Cursor
You follow these steps when using a cursor:
• Declare Variables to Store the Column Values from
the Select Statement
•
•
These variables must be compatible with the column
types for the retrieved rows.
Example:
DECLARE @MyProductID int
DECLARE @MyProductName nvarchar (40)
DECLARE @MyUnitPrice money
Using Cursor
• Declare the Cursor
•
•
•
A cursor declaration consists of a name that you assign
to the cursor and a SELECT statement that you want to
execute retrieved rows.
This SELECT statement is not actually run until you
open the cursor.
Example:
DECLARE ProductCursor CURSOR for
Select ProductID, ProductName, UnitPrice
from Products
Where ProductID<=10
Using Cursor
•
•
Open the Cursor
You open a cursor using the OPEN statement.
OPEN ProductCursor
Fetch the Rows from the Cursor
• To read each row from your cursor, use the fetch statement
• Since there might be many rows, you need to usee While loop.
• To check end of loop, use @@FETCH_STATUS function. This
function returns:
• 0 if FETCH statement successfully returned a row.
• -1 if FETCH statement failed or the requested row is
outside the result set.
• -2 if Row fetched is missing
Using Cursor
The following example shows a loop that reads each row from ProductCursor.
While @@Fetch_Status=0
Begin
Fetch Next from ProductCursor
Into @MyProductID, @MyProductName, @MyUnitPrice
Print ‘@MyProductID=‘ + Convert (nvarchar, @MyProductID)
Print
‘@MyProductName=‘
+
Convert
(nvarchar,
@MyProductName)
Print ‘@MyUnitPrice=‘ + Convert (nvarchar, @MyUnitPrice)
END
Using Cursor
• Close the Cursor
You close a cursor using the CLOSE statement.
CLOSE ProductCursor
You should also remoce the reference to your curs
or using the DEALLOCATE statement.
DEALLOCATE ProductCursor
Functions
• There are many built-in functions to use in TSQL.
• You can find detailed explanation about these
functions from the web site below:
http://msdn.microsoft.com/enus/library/ms174318.aspx
Creating User-Defined Functions
• You create a function using the CREATE
FUNCTION statement.
• There are three types of user-defined functions:
•
•
•
Scalar Functions: These functions returns a single
value.
Inline Table-valued Functions: Returns an object of
the table type. You can think of a table a a regular
database table, except it is stored in memory. An
inline table-valued function can return the results
retrived by only a single SELECT statement.
Multistatement table-valued Function: Returns a
object of table type and it can contain multiple TSQL statement.
Creating User-Defined Functions
Scalar Functions: The example below creates the DiscountPrice()
function, which returns the original price of an item multipled by a
discount factor.
Create Function DiscountPrice(@OriginalPrice money, @Discount money)
Returns Money
As
Begin
Return @OriginalPrice * @Discount
End
Using this function:
Declare @MyDiscountFactor Float
Set @MyDiscountFactor =0.3
Select dbo.DiscountPrice(UnitPrice,@MyDiscountFactor ), UnitPrice
From Products
Where ProductID=1
Creating User-Defined Functions
Inline Table-Valued Functions: Inline userdefined functions are a subset of user-defined
functions that return a table.
Inline functions can be used to achieve the
functionality of parameterized views.
Consider this view:
CREATE VIEW vw_CustomerNamesInWA AS
SELECT CustomerID, CompanyName
FROM Northwind.dbo.Customers
WHERE Region = 'WA'
Creating User-Defined Functions
Inline Table-Valued Functions: You can
create a more generalized version,
vw_CustomerNamesInRegion, by replacing
the WHERE Region = 'WA' with a WHERE
Region = @RegionParameter and letting
users specify the region they are interested
in viewing. Views, however, do not support
parameters in the search conditions
specified in the WHERE clause.
Creating User-Defined Functions
CREATE FUNCTION fn_CustomerNamesInRegion
( @RegionParameter nvarchar(30) )
RETURNS table
AS
RETURN (
SELECT CustomerID, CompanyName
FROM Northwind.dbo.Customers
WHERE Region = @RegionParameter
)
-- Example of calling the function for a specific region
SELECT *
FROM fn_CustomerNamesInRegion(N'WA')
Creating User-Defined Functions
Inline Table-Valued Functions: The example below creates the
ProductsToBeReordered() function, which returns a table containing the rows
from the Products table
Create Function ProductsToBeReordered (@ReorderLevel int)
Returns Table
As
Return
(
Select *
From Products
Where UnitsInStock<=@ReorderLevel
)
Using this function:
Select ProductID, ProductName, UnitsInStock
From ProductsToBeReordered(10)
Where ProductId<=50;
Creating User-Defined Functions
Multistatement Table-Valued Functions: User-defined functions that return a table
can be powerful alternatives to views. A user-defined function that returns a table
can be used where table or view expressions are allowed in Transact-SQL
queries. While views are limited to a single SELECT statement, user-defined
functions can contain additional statements that allow more powerful logic than is
possible in views.
In a user-defined function that returns a table:
• The RETURNS clause defines a local return variable name for the table
returned by the function. The RETURNS clause also defines the format of the
table. The scope of the local return variable name is local within the function.
• The Transact-SQL statements in the function body build and insert rows into
the return variable defined by the RETURNS clause.
• When a RETURN statement is executed, the rows inserted into the variable
are returned as the tabular output of the function. The RETURN statement
cannot have an argument.
Creating User-Defined Functions
This example creates a function in the Northwind database that returns a table:
CREATE FUNCTION LargeOrderShippers ( @FreightParm money )
RETURNS @OrderShipperTab TABLE
(
ShipperID int,
ShipperName nvarchar(80),
OrderID
int,
ShippedDate datetime,
Freight
money
)
AS
This query references the table
BEGIN
returned by the function in its
INSERT @OrderShipperTab
FROM clause:
SELECT *
SELECT S.ShipperID, S.CompanyName,
FROM LargeOrderShippers( $500 )
O.OrderID, O.ShippedDate, O.Freight
FROM Shippers AS S INNER JOIN Orders AS O
ON S.ShipperID = O.ShipVia
WHERE O.Freight > @FreightParm
RETURN
END
Creating User-Defined Functions
Multistatement Table-Valued Functions: The example below creates the
ProductsToBeReordered2() function, which returns a table containing the rows
from the Products table
Create Function ProductsToBeReordered2 (@ReorderLevel int)
Returns @MyProducts table
(
ProductID int, ProductName nvarchar(40), UnitsInStock smallint, Reorder nvarchar(3)
)
As
Begin
Insert into @MyProducts
Select ProductID, ProductName, UnitsInStock, ‘no’ from Products
Update @MyProducts
Set Reorder=‘yes’
Where UnitsInStock<=@ReorderLevel
Return
End
Usage of the function
Select * from ProductsToBeReordered2(20)
Stored Procedures
• A stored procedure is a named collection
of SQL statements language.
• You can create stored procedures for
commonly used functions and to increase
performance.
• SQL Server also provides system
procedures to perform administrative
tasks and to update the system tables.
How Stored Procedures Work
• When you run a stored procedure, Server
prepares an execution plan so that the
procedure’s execution is very fast. Stored
procedures can:
•
•
•
•
•
Take parameters
Call other procedures
Return a status value to a calling procedure or
batch to indicate success or failure and the
reason for failure
Return values of parameters to a calling
procedure or batch
Be executed on remote Servers
How Stored Procedures Work
• The ability to write stored procedures greatly
enhances the power, efficiency, and flexibility
of SQL.
• Compiled procedures dramatically improve
the performance of SQL statements and
batches.
• In addition, stored procedures on other
Servers can be executed if both your server
and the remote server are set up to allow
remote logins.
Why Stored Procedures Are Faster
When you submit any command for SQL Server to
execute, it goes through the following stages:
• The command is parsed for syntax. Any commands
that are syntactically incorrect are rejected.
• The command is then translated into an internal
format known as a sequence tree or query tree.
• The command is optimized based on estimated
performance costs, and an execution plan is
generated from the sequence tree that contains all
the necessary steps to check constraints and carry
out the command.
• The command is executed.
Why Stored Procedures Are Faster
• If you send direct SQL statements one at a
time from your application, then each
statement requires all four stages for each
single statement.
• The execution plan is not saved with the
stored procedure when it is created.
• For a stored procedure, stages 1 and 2
happen only once, when you save the stored
procedure.
• Step 3 occurs only if the execution plan is not
• already in the memory cache. Subsequent
executions only need step 4
Creating and Using Stored Procedures
• The syntax for creating a simple stored
procedure, without special features such as
parameters, is:
create procedure procedure_name
as SQL_statements
Sample:
create procedure namelist
as select name from sysusers
To execute the procedure
• namelist
• execute namelist
• exec namelist
Creating and Using Stored Procedures
To execute a stored procedure on a remote
Server, you must give the server name. The
full syntax for a remote procedure call is:
execute
server_name.[database_name].[owner].proc
edure_name
Creating and Using Stored Procedures
A procedure can include more than one statement.
create procedure showall as
select count(*) from sysusers
select count(*) from sysobjects
select count(*) from syscolumns
When a create procedure command is successfully
executed, the procedure’s name is stored in
sysobjects, and its source text is stored in
syscomments.
You can display the source text of a procedure with
sp_helptext:
sp_helptext showall
PUBS2 DB Schema
Stored Procedures and Performance
The queries used by stored procedures and
triggers are optimized only when they are
compiled. As indexes or other changes that
affect statistics are made to the database,
compiled stored procedures and triggers may
lose efficiency. By recompiling stored
procedures and triggers that act on a table,
you can reoptimize the queries.
sp_recompile showall
Creating and Using Stored Procedures
The complete syntax for create procedure is:
create procedure [owner.]procedure_name[;number]
[(]@parameter_name
datatype [(length) | (precision [, scale ])]
[= default] [output]
[, @parameter_name
datatype [(length) | (precision [, scale])]
[= default] [output]]...[)]
[with recompile]
as {SQL_statements | external name dll_name}
You can create a procedure in the current database
only.
Creating and Using Stored Procedures
Here is the complete syntax statement for execute:
[exec[ute]] [@return_status = ]
[[[server.]database.]owner.]procedure_name[;number]
[[@parameter_name =] value |
[@parameter_name =] @variable [output]
[, [@parameter_name =] value |
[@parameter_name =] @variable [output]...]]
[with recompile]
Creating and Using Stored Procedures
Example: Given an author’s last and first names, the procedure displays the
names of any books written by that person and the name of each book’s
publisher.
create proc au_info @lastname varchar(40),
@firstname varchar(20) as
select au_lname, au_fname, title, pub_name
from authors, titles, publishers, titleauthor
where au_fname = @firstname
and au_lname = @lastname
and authors.au_id = titleauthor.au_id
and titles.title_id = titleauthor.title_id
and titles.pub_id = publishers.pub_id
Execution:
au_info Ringer, Anne
Creating and Using Stored Procedures
Example: The following stored procedure queries the system tables. Given
a table name as the parameter, the procedure displays the table name,
index name, and index ID.
create proc showind @table varchar(30) as
select table_name = sysobjects.name,
index_name = sysindexes.name, index_id = indid
from sysindexes, sysobjects
where sysobjects.name = @table
and sysobjects.id = sysindexes.id
Execution:
execute showind titles
exec showind titles
execute showind @table = titles
execute GATEWAY.pubs2.dbo.showind titles
showind titles or execute showind titles
Creating and Using Stored Procedures
If you supply the parameters in the form “@parameter = value” you can
supply them in any order. Otherwise, you must supply parameters in the
order of their create procedure statement. If you supply one value in the
form “@parameter = value”, then supply all subsequent parameters this
way.
Below procedure displays the datatype of the qty column from the
salesdetail table.
create procedure showtype @tabname varchar(18), @colname
varchar(18) as
select syscolumns.name, syscolumns.length,
systypes.name from syscolumns, systypes, sysobjects
where sysobjects.id = syscolumns.id and @tabname =
sysobjects.name and @colname = syscolumns.name and
syscolumns.type = systypes.type
Execution: exec showtype @colname = qty , @tabname = salesdetail
Creating and Using Stored Procedures
You can assign a default value for the parameter in the create procedure
statement. This value, which can be any constant, is used as the argument to the
procedure if the user does not supply one.
Here is a procedure that displays the names of all the authors who have written
a book published by the publisher given as a parameter. If no publisher name
is supplied, the procedure shows the authors published by Algodata Infosystems.
create proc pubinfo
@pubname varchar(40) = "Algodata Infosystems" as
select au_lname, au_fname, pub_name
from authors a, publishers p, titles t, titleauthor ta
where @pubname = p.pub_name
and a.au_id = ta.au_id
and t.title_id = ta.title_id
and t.pub_id = p.pub_id
exec pubinfo
Creating and Using Stored Procedures
This procedure, showind2, assigns “titles” as the default value for the
@tableparameter:
create proc showind2
@table varchar(30) = titles as
select table_name = sysobjects.name,
index_name = sysindexes.name, index_id = indid
from sysindexes, sysobjects
where sysobjects.name = @table
and sysobjects.id = sysindexes.id
The column headings, for example, table_name, clarify the result
display. Here is what showind2 shows for the authors table:
showind2 authors
Showind2 Server uses the default, titles
Creating and Using Stored Procedures
In the create procedure statement, you can declare null as the default value for individual
parameters:
create procedure showind3
@table varchar(30) = null as
if @table is null
print "Please give a table name."
else
select table_name = sysobjects.name,
index_name = sysindexes.name,
index_id = indid
from sysindexes, sysobjects
where sysobjects.name = @table
and sysobjects.id = sysindexes.id
The column headings, for example, table_name, clarify the result display. Here is what
showind2 shows for the authors table:
showind3 authors
Showind3
Creating and Using Stored Procedures
Using more than one parameter:
create proc au_info2
@lastname varchar(30) = "D%",
@firstname varchar(18) = "%" as
select au_lname, au_fname, title, pub_name
from authors, titles, publishers, titleauthor
where au_fname like @firstname
and au_lname like @lastname
and authors.au_id = titleauthor.au_id
and titles.title_id = titleauthor.title_id
and titles.pub_id = publishers.pub_id
Execution:
au_info2
au_info2 Ringer
Returning information from stored
procedures
Stored procedures can return the following types of
information:
• Return status – indicates whether or not the
stored procedure completed successfully.
• proc role function – checks whether the
procedure was executed by a user with sa_role,
sso_role, or ss_oper privileges.
• Return parameters – report the parameter values
back to the caller, who can then use conditional
statements to check the returned value.
Returning information from stored
procedures
Return status: Stored procedures report a return status
that indicates whether or not they completed
successfully, and if they did not, the reasons for failure.
This value can be stored in a variable when a
procedure is called, and used in future Transact-SQL
statements.
Here is an example of a batch that uses the form of the
execute statement that returns the status:
declare @status int
execute @status = byroyalty 50
select @status
Returning information from stored
procedures
Returning information from stored
procedures
User-generated return values: You can generate your own
return values in stored procedures by adding a parameter to
the return statement. The following example returns 1 when
a book has a valid contract and returns 2 in all other cases:
create proc checkcontract @titleid tid
as
if (select contract from authors where title_id = @titleid)
=1
return 1
else
return 2
Returning information from stored
procedures
The following stored procedure calls checkcontract, and uses conditional
clauses to check the return status:
create proc get_au_stat @titleid tid
as
declare @retvalue int
execute @retvalue = checkcontract @titleid
if (@retvalue = 1)
print "Contract is valid."
else
print "There is not a valid contract."
Here are the results when you execute get_au_stat with the title_id of a
book with a valid contract:
get_au_stat MC2222
Contract is valid
Returning information from stored
procedures
Return parameters: Another way that stored procedures can return information to the
caller is through return parameters. The caller can then use conditional statements to
check the returned value.
This stored procedure performs multiplication on two integers (the third
integer, @result, is defined as an output parameter):
create procedure mathtutor
@mult1 int, @mult2 int, @result int output
as
select @result = @mult1 * @mult2
To use mathtutor to figure a multiplication problem, you must declare the @result variable
and include it in the execute statement. Adding the output keyword to the execute
statement displays the value of the return parameters.
declare @result int
exec mathtutor 5, 6, @result output
select @result
Returning information from stored
procedures
Return parameters: This stored procedure checks to determine whether new book
sales would cause an author’s royalty percentage to change (the @pc parameter is
defined as an output parameter):
create proc roy_check @title tid, @newsales int, @pc int output
as
declare @newtotal int
select @newtotal = (select titles.ytd_sales + @newsales
from titles where title_id = @title)
select @pc = royalty from roysched
where @newtotal >= roysched.lorange and @newtotal
roysched.hirange and roysched.title_id = @title
<
Triggers
•
•
•
•
•
•
•
•
•
How triggers work
Creating triggers
Using triggers to maintain referential integrity
Multirow considerations
Nesting triggers
Rules associated with triggers
Disabling triggers
Dropping triggers
Getting information about triggers
How Triggers Work
• A trigger is a stored procedure that goes into
effect when you insert, delete, or update data in a
table.
• You can use triggers to perform a number of
automatic actions, such as
•
•
•
•
cascading changes
enforcing column restrictions
comparing the results of data modifications
maintaining the referential integrity of data across a
database.
How Triggers Work
• A trigger is specific to one or more of the
data modification operations, update,
insert, and delete and is executed once for
each SQL statement.
How Triggers Work
•
For example, to prevent users from removing any
publishing companies from the publishers table, you
could use this trigger:
create trigger del_pub
on publishers
for delete
as
begin
rollback transaction
print ‘You cannot delete any publishers!’
end
How Triggers Work
• A trigger fires only after the data
modification statement has completed and
Server has checked for any data type, rule, or
integrity constraint violation.
• The trigger and the statement that fires it are
treated as a single transaction that can be
rolled back from within the trigger.
• If Server detects a severe error, the entire
transaction is rolled back.
How Triggers Work
Triggers are most useful in these situations:
• Triggers can cascade changes through related
tables in the database.
• Triggers can disallow, or roll back, changes that
would violate referential integrity, canceling the
attempted data modification transaction.
• Triggers can enforce restrictions that are much
more complex than those that are defined with
rules.
• Triggers can perform simple “what if” analyses.
For example, a trigger can compare the state of a
table before and after a data modification and take
action based on that comparison.
Creating Triggers
Here is the complete create trigger syntax:
create trigger [owner.]trigger_name
on [owner.]table_name
{for / instead of {insert , update , delete}
as SQL_statements
Or, using the if update clause:
create trigger [owner.]trigger_name
on [owner.]table_name
for {insert , update}
as
[if update (column_name )
[{and | or} update (column_name )]...]
SQL_statements
[if update (column_name )
[{and | or} update (column_name )]...
SQL_statements ]...
Creating Triggers
SQL statements that are not allowed in triggers:
Since triggers execute as part of a transaction, the following statements are not
allowed in a trigger:
• All create commands, including create database, create table, create index,
create procedure, create default, create rule, create trigger, and create view
• All drop commands
• alter table and alter database
• truncate table
• grant and revoke
• update statistics
• reconfigure
• load database and load transaction
• disk init, disk mirror, disk refit, disk reinit, disk remirror, disk unmirror
• select into
Trigger Test Tables
•
•
•
Referential integrity triggers keep the
values of foreign keys in line with those in
primary keys.
When a data modification affects a key
column, triggers compare the new column
values to related keys by using temporary
work tables called trigger test tables.
When you write your triggers, you base
your comparisons on the data that is
temporarily stored in the trigger test
tables.
Trigger Test Tables
Server uses two special tables in trigger
statements: the deleted table and the inserted
table. These are temporary tables used in trigger
tests.
• The deleted table stores copies of the
affected rows during delete and update
statements. During the execution of a delete
or update statement, rows are removed from
the trigger table and transferred to the
deleted table. The deleted and trigger tables
ordinarily have no rows in common.
Trigger Test Tables
• The inserted table stores copies of the affected
rows during insert and update statements.
During an insert or an update, new rows are
added to the inserted and trigger tables at the
same time. The rows in inserted are copies of
the new rows in the trigger table.
• An update is a delete followed by an insert; the
old rows are copied to the deleted table first;
then the new rows are copied to the trigger
table and to the inserted table.
Insert Trigger Example
• When you insert a new foreign key row, make sure
the foreign key matches a primary key. The trigger
should check for joins between the inserted rows
(using the inserted table) and the rows in the primary
key table, and then roll back any inserts of foreign
keys that do not match a key in the primary key table.
• The following trigger compares the title_id values
from the inserted table with those from the titles
table. It assumes that you are making an entry for the
foreign key and that you are not inserting a null value.
If the join fails, the transaction is rolled back.
Insert Trigger Example
create trigger forinsertrig1
on salesdetail
for insert
as
if (select count(*)
from titles, inserted
where titles.title_id = inserted.title_id) !=
@@rowcount
/* Cancel the insert and print a message.*/
begin
rollback transaction
print ‘No, the title_id does not exist in titles.’
end
/* Otherwise, allow it. */
else
print ‘Added! All title_id’s exist in titles.’
Insert Trigger Example
• @@rowcount (Returns the number of rows
affected by the last statement) refers to the
number of rows added to the salesdetail table.
This is also the number of rows added to the
inserted table. The trigger joins titles and
inserted to determine whether all the title_ids
added to salesdetail exist in the titles table. If
the number of joined rows, which is determined
by the select count(*) query, differs from
@@rowcount, then one or more of the inserts
is incorrect, and the transaction is canceled.
Insert Trigger Example
• This trigger prints one message if the insert is
rolled back and another if it is accepted. To
test for the first condition, try this insert
statement:
insert salesdetail
values ("7066", "234517", "TC9999", 70, 45)
To test for the second condition, enter:
insert salesdetail
values ("7896", "234518", "TC3218", 75, 80)
Delete Trigger Example
• When you delete a primary key row, delete
corresponding foreign key rows in dependent
tables. This preserves referential integrity by
ensuring that detail rows are removed when
their master row is deleted. If you do not delete
the corresponding rows in the dependent
tables, you may end up with a database with
detail rows that cannot be retrieved or
identified. To properly delete the dependent
foreign key rows, use a trigger that performs a
cascading delete.
Cascading Delete Example
• When a delete statement on titles is
executed, one or more rows leave the titles
table and are added to deleted. A trigger can
check the dependent tables—titleauthor,
salesdetail, and roysched—to see if they
have any rows with a title_id that matches
the title_ids removed from titles and is now
stored in the deleted table. If the trigger
finds any such rows, it removes them.
Cascading Delete Example
create trigger delcascadetrig
on titles
for delete
as
delete titleauthor
from titleauthor, deleted
where titleauthor.title_id = deleted.title_id
/* Remove titleauthor rows that match deleted (titles) rows.*/
delete salesdetail
from salesdetail, deleted
where salesdetail.title_id = deleted.title_id
/* Remove salesdetail rows that match deleted ** (titles) rows.*/
delete roysched
from roysched, deleted
where roysched.title_id = deleted.title_id
/* Remove roysched rows that match deleted
** (titles) rows.*/
Update Trigger Examples
The following example cascades an update from the primary table titles to the dependent tables
titleauthor and roysched.
create trigger cascade_utrig
on titles
for update as
if update(title_id)
begin
update titleauthor
set title_id = inserted.title_id from titleauthor, deleted, inserted
where deleted.title_id = titleauthor.title_id
update roysched
set title_id = inserted.title_id from roysched, deleted, inserted
where deleted.title_id = roysched.title_id
update salesdetail
set title_id = inserted.title_id from salesdetail, deleted, inserted
where deleted.title_id = salesdetail.title_id
end
Update Trigger Examples
To test this trigger, suppose that the book Secrets of Silicon Valley was reclassified to a
psychology book from popular_comp. The following query updates the title_id PC8888 to
PS8888 in titleauthor, roysched, and titles.
update titles
set title_id = ‘PS8888’
where title_id = ‘PC8888’
Disabling triggers
The insert, update, and delete commands normally fire any trigger they
encounter, which increases the time needed to perform the operation. To
disable triggers during bulk insert, update, or delete operations, you can
use the disable trigger option of the alter table command. You can use the
disable trigger option either to disable all the triggers associated with the
table, or to specify a particular trigger to disable. However, any triggers
you disable will not be fired after the copy is complete. For example:
alter table [database_name.[owner_name].]table_name
{enable | disable } trigger [trigger_name]
Disable
alter table pubs2
disable del_pubs
Enable:
alter table pubs2
enable del_pubs
Dropping triggers
You can remove a trigger by dropping it or by dropping the
trigger table with which it is associated.
The drop trigger syntax is:
drop trigger [owner.]trigger_name [, [owner.]trigger_name]...
When you drop a table, Server drops any triggers associated
with it. drop trigger permission defaults to the trigger table
owner and is not transferable.
Getting information about triggers
As database objects, triggers are listed in sysobjects by name.
The type column of sysobjects identifies triggers with the
abbreviation “TR”. This query finds the triggers that exist in a
database:
select *
from sysobjects
where type = "TR"
The source text for each trigger is stored in syscomments.
Execution plans for triggers are stored in sysprocedures. The
system procedures described in the following sections provide
information from the system tables about triggers.
Getting information about triggers
sp_help: You can get a report on a trigger using sp_help. For
example, you can get information on deltitle as follows:
sp_help deltitle
sp_helptext: To display the source text of a trigger, execute
sp_helptext, as follows:
sp_helptext deltitle
sp_depends: lists the triggers that reference an object or all
the tables or views that the trigger affects. This example
shows how to use sp_depends to get a list of all the objects
referenced by the trigger deltitle:
sp_depends deltitle