Stored Procedures and Triggers

Selects

Objectives

  • Understand stored procedure syntax
  • Understand error trapping
  • Understand selection structures

Description

Basics

A stored procedure is a database object that can store several SQL commands in a single executable script. It can receive parameters, set variables, check for errors and incorporate branching (IF) and looping structures. Stored procedures also provide a powerful tool for protecting database integrity and security. It is possible to grant a user permissions to execute a stored procedure while not granting that user permission to access the underlying tables. This allows the database administrator to control the ways in which any user can access a database.

Stored procedures are database specific. SQL Server's Transact SQL(T-SQL) supports them as does Oracle's Procedural Language SQL (PL-SQL), but MySQL and Access do not support them. MySQL 5.0 added stored procedure support to MySql, and Access' parameter queries have some of the qualities of a stored procedure.

In SQL Server the basic pattern for a stored procedure is:

CREATE PROC [name of procedure]
[@parameter1] [datatype],
[@parameter2] [datatype]
AS
[SQL statements and any variables]

You can have as many parameters as you need. They are passed to the procedure when it is called. All parameters and variables in a stored procedure start with the @ sign. Built-in fuctions such as @@Identity which returns the last identity (counter) use double @ signs.

Here is a simple stored procedure using Northwind:

Create proc usp_EmployeeSales
@Lastname nvarchar(20)
AS
Select Lastname, count(orderid)
From Employees e
Inner join Orders o
On e.Employeeid=o.employeeid
Where lastname=@Lastname
group by Lastname

@Lastname is a parameter. It must be passed to the stored procedure when you call it.

usp_EmployeeSales 'Davolio'

You can procede the name of the stored procedure with the command exec if you wish. the procedure runs the same with or without it, but the exec key word may make it clearer in the code that you are executing a procedure. The results look like this:

Davolio	123

Sample Database

For the rest of the Stored Procedure examples we will use a database called SimpleInventory. The database consists of 4 tables: a Customer table, an Iinventory table, a Sales and a SaleDetail table.

Here is the code to create the database. You can copy it from this document and paste it into the query analyzer, or go to the ITC 222 syllabus on my web site and download the script under Sample Code.

/***********************************
this script creates the database 
simple inventory. It consists of four
tables a few records. Its purpose is
to provide a simple test database
for creating stored procedures and
triggers
Steve Conger Spring 2005
***********************************/

Create database SimpleInventory
go
use SimpleInventory
go
--begin tables
Create table Customer
(
  Customerid int identity(100,1) primary key,
  lastname varchar(20) not null,
  firstname varchar(20) not null
)
go
create table Inventory
(
  InventoryID int identity(1000,1) primary key,
  InventoryItem varchar(40) not null,
  InventoryPrice money not null,
  InventoryInStock int not null
)
go
Create table Sale
(
  SaleID int identity(1,1)primary key,
  SaleDate datetime not null,
  CustomerID int not null,
  Constraint FKCustomer Foreign Key(customerid)
  References Customer(customerID)
)

go
Create table SaleDetail
(
  SaleDetailID int identity(1,1) primary key,
  SaleID int not null,
  InventoryID int not null,
  Quantity int not null,
  Constraint FKSale Foreign key(saleid)
  References Sale(saleid),
  Constraint FKInventory Foreign Key(InventoryID)
  References Inventory(InventoryID)
)

--begin insert into tables
go

--inserts into customer
Insert into customer(lastname, firstname)
values ('Sanders', 'Sara')
Insert into customer(lastname, firstname)
values ('williams', 'Mark')
Insert into customer(lastname, firstname)
values ('Barnes', 'Nobel')

--Insert into inventory
Insert into Inventory(InventoryItem, InventoryPrice, InventoryInStock)
Values ('Plasma TV', 1870.00,10)
Insert into Inventory(InventoryItem, InventoryPrice, InventoryInStock)
Values ('Laptop', 1270.00,10)
Insert into Inventory(InventoryItem, InventoryPrice, InventoryInStock)
Values ('XBox', 288.50,10)
Insert into Inventory(InventoryItem, InventoryPrice, InventoryInStock)
Values ('Ipod', 279.00,10)
Insert into Inventory(InventoryItem, InventoryPrice, InventoryInStock)
Values ('DVD Recorder', 377.00,10)

Adding Variables

Lets create a more complex and useful stored procedure. We will do it over several iterations, each time adding features that make it more useful. The basic idea is to process a sale. We will start by assuming the customer already exists in the database and that we know his or her ID. To complete the sale we must

  1. Insert it and the current date into the sale table
  2. Get the new saleid
  3. Insert the saleid , the InventoryID, and the quantity sold into the saledetail table
  4. Subtract the quantity sold from the InventoryInStock

For now we will assume there is only one item for each sale in the saledetail table. First we will create the new procedure and assign the parameters

Create proc usp_NewSale
@CustomerID int,
@InventoryID int,
@Quantity int

Now we will start the procedure itself using the AS keyword. the first task is to declare a variable to get the current date and time. Parameters are always declared before the AS, Variables are declared after the AS and must use the DECLARE keyword. To assign a value to a variable you must use a SELECT statement.

As
Declare @today datetime
Select @today= GetDate()

Now We will do the first insert using the parameter @CustomerID and the variable @today.

Insert into Sale(SaleDate, CustomerID)
Values(@today, @customerid)

Now we need to insert into the SaleDetail table. To do that we need to know the SaleId of the sale we just inserted. We can declare a new variable and assign to it the value retrieved by the built in function @@Identity. @@Identity returns the last generated Identity number in the database. With this we can insert into sale details.

Declare @saleid int
Select @saleid=@@identity
Insert into SaleDetail(saleid,InventoryID,Quantity)
Values(@saleid, @inventoryID, @quantity)

Finally we need to update the Inventory.

Update Inventory
Set InventoryInStock=InventoryInStock-@Quantity
where InventoryID=@inventoryID

Run the SQL to create the stored procedure. If you have errors, correct them. If the stored procedure SQL runs but there are still problems to correct, change the CREATE PROC to ALTER PROC and make the changes and then run the SQL again.

To execute the Procedure state its name and the list the parameter value in the order they are declared.

usp_NewSale 101,1001,3

Alternatively, you can assign the values directly to the parameter name and then the sequence of values doesn’t matter. (Warning, each time you run this you will subtract an additional 3 items from inventory item 1001)

EXEC usp_NewSale @CustomerID=101, @InventoryID=1001, @Quantity=3

Now do some selects to see the results.

Select * from Inventory
Select * from Sale
select * from SaleDetail

If and Exists

Now we will alter the store procedure to test whether the customer exists in the database. and we will use the names of the customers rather than the IDs. This introduces the new keyword IF, EXISTS, BEGIN, END, ELSE. The BEGIN and END keywords mark the beginning and ending of blocks of code. Here is the complete stored procedure with SQL comments to help explain what is going on.

The code is fairly complex. You can either type it into the Query analyzer or cut and paste it. Try to identify what each section is doing.

Alter proc usp_NewSale
--add new parameters
@Lastname varchar(20),
@firstname varchar(20),
@InventoryID int,
@Quantity int
As
Declare @today datetime
Select @today= GetDate()
--declare customerid as variable
Declare @CustomerID int
--set up a condition if the name
--exists do one thing
--if not do another
IF Exists 
  (SELECT Lastname, firstname 
  FROM Customer
  WHERE Lastname=@lastname
  AND firstname=@firstname)
 --begin the block for if the name does exist 
Begin  
  --find the existing id
  Select @CustomerID=Customerid
  FROM Customer
  Where lastname=@lastname
  And firstname=@firstname
end --end the block for does exist
Else --start the block for does not exist

Begin
 --insert the new customer
  Insert into Customer (lastname, firstname)
  Values (@Lastname, @firstname)
  --get the identity
  Select @customerID=@@identity
end --end the block for does not exist
  --do the remaining inserts and updates
  Insert into Sale(SaleDate, CustomerID)
  Values(@today, @Customerid)
  Declare @saleid int
  Select @saleid=@@identity
  Insert into SaleDetail(saleid,InventoryID,Quantity)
  Values(@saleid, @inventoryID, @quantity)
  Update Inventory
  Set InventoryInStock=InventoryInStock-@Quantity
  where InventoryID=@inventoryID

When you get the stored procedure error free, run it with the following parameters

exec usp_Newsale 'zukof', 'Albert', 1002, 1

Now run these selects to view the results.

Select * from customer
Select * from Inventory
Select * from Sale
select * from SaleDetail

Now run it again with these parameters (an existing customer) and do the selects to see the results

exec usp_Newsale 'Barnes', 'Nobel', 1002, 1

Error Trapping and Transactions

So far. we have added a simple branching to handle two different situations: one where the customer exists, and one where the customer does not exit. It would be very useful to add a transaction to the stored procedure so that if there are any errors the whole thing could be rolled back. Otherwise we could end up with a sale without sale details, or the Inventory might not get updated. So we will adopt the stored procedure to include transactions and error trapping.

SQL server 2005 adds the ability to use try catch blocks. In a Try catch block, you try the code and then catch any errors in a catch block. If you use the Try Catch with a transaction object you can commit the transaction if there is no error and roll it back if there is.

Alter proc usp_NewSale
--add new parameters
@Lastname varchar(20),
@firstname varchar(20),
@InventoryID int,
@Quantity int
As
Declare @today datetime
Select @today= GetDate()
--declare customerid as variable
Declare @CustomerID int

Begin tran --begin the transaction
Begin Try --begin try catch

--set up a condition if the name
--exists do one thing
--if not do another

IF Exists 
  (SELECT Lastname, firstname 
  FROM Customer
  WHERE Lastname=@lastname
  AND firstname=@firstname)
 --begin the block for if the name does exist 
Begin  
  --find the existing id
  Select @CustomerID=Customerid
  FROM Customer
  Where lastname=@lastname
  And firstname=@firstname
end --end the block for does exist
Else --start the block for does not exist

Begin
 --insert the new customer
  Insert into Customer (lastname, firstname)
  Values (@Lastname, @firstname)
  --get the identity
  Select @customerID=@@identity
end --end the block for does not exist
  --do the remaining inserts and updates
  Insert into Sale(SaleDate, CustomerID)
  Values(@today, @Customerid)
  Declare @saleid int
  Select @saleid=@@identity
  Insert into SaleDetail(saleid,InventoryID,Quantity)
  Values(@saleid, @inventoryID, @quantity)
  Update Inventory
  Set InventoryInStock=InventoryInStock-@Quantity
  where InventoryID=@inventoryID
 commit tran
 End Try
 Begin Catch
 Rollback tran
 End catch

Try this with the values 100 for the customer and 1007 for the InventoryID. (The inventoryID doesn’t exist). Try it with a bad customer number. Try it with legitimate numbers.

Try Catch

SQL server 2005 adds the ability to use try catch blocks. This can simplify error handling greatly. Below is the same query as above but with the try catch error trapping

Create proc usp_Errortest
--the parameters
@Customerid int,
@InventoryID int
AS
--Declare some variables
Declare @SaleDate DateTime
Select @SaleDate=GetDate()
Declare @SaleID int
--begin the transaction
Begin tran
--begin the try
Begin Try
--insert into Sale
Insert into sale (Customerid, Saledate)
Values (@CustomerID, @SaleDate)
--insert into SaleDetail
Insert into SaleDetail(Saleid, InventoryID, Quantity)
Values(@saleid, @inventoryID, 1)
Commit Tran
End Try
--catch any errors
Begin Catch
rollback tran
Print 'the record was not inserted'
End catch

Triggers

Description

Triggers are like stored procedures except that they dont take parameters and they are executed--triggered--by one of three events: Insert, Update, and or Delete. Triggers can be used to enforce referential integrity rules (though Primary Key and Foreign Key constraints are a better way.). They can also be used to enforce restrictions on the values entered into a field (though again Check constraints are a better option). Probably the most important use of triggers is to enforce business rules that cannot be built into the referential integrity rules of the database.

In SQL Server2005 there are several kinds of Triggers

  • FOR triggers
  • AFTER triggers
  • INSTEAD OF triggers
  • DDL Triggers

DDL triggers are triggered by events like Create Table, Creat Procedure, Create User, etc. We are not going to look at that kind of trigger

FOR triggers and AFTER triggers are actually synonymous. Both execute after the triggering action is completed. INSTEAD OF triggers execute instead of the triggering command.The syntax for creating a trigger is similar to a procedure:

CREATE TRIGGER [trigger name] ON [tablename]
[FOR, AFTER or INSTEAD OF] --that is one of these
[UPDATE, INSERT, DELETE] --one or more of these
AS
[SQL statements]

To make changes to the Trigger change the CREATE to ALTER or DROP the trigger and run the CREATE again.

Here is an example using the SimpleInventory database of an After trigger that checks the InventoryInStock field after an update and returns a message if the number in inventory is below a certain number.

Create trigger StockNotice on Inventory
After update
As
Declare @InStock int
Declare @InventoryItem varchar(40)
Declare @InventoryID int
Select @InventoryID=InventoryID
from Inserted
Select @inventoryItem=inventoryitem, @instock=InventoryInStock 
from Inventory
where InventoryID=@InventoryID
If (@inStock < 3)
Begin
Print 'Order more of this item'
print @inventoryitem
end

Inserted is a temporary table created by SQL Server for every Insertion and Update. Only triggers can access this temporary table.

If you save the trigger and run the following code you can trigger the trigger and see how it works.

Update Inventory
Set InventoryInStock=2
where InventoryID=1000

Just as INSERT and UPDATE have a temporary table Inserted, DELETE has a temporary table called Deleted. The Following trigger uses the deleted table to create a table to store deleted items for later. I have added comments to explain the code logic.

Create trigger tr_SaveDelete on Customer
For Delete --exicutes after the delete
As
--check to see if the custtemp table exists
--if not create it
if  not exists (select name from sys.Tables where name='CustTemp')
Begin
Create Table CustTemp
(
  CustomerID int,
  Lastname varchar(20),
  Firstname varchar(20)
)
End
Insert into custTemp (CustomerID, Lastname, firstname)
Select CustomerID, Lastname, firstname from Deleted

To test this trigger, first insert an new customer into the customer table and then delete him or her. (You may not be able to delete the existing customers because of relational integrity constraints).

Select from the customer table to see if the deletion has occurred. Then select from the CustTemp table to see the deleted row. Insert another customer and delete him or her again to see the trigger operate with the custTemp table already in existence.

Instead of triggers

As mentioned before, Instead of triggers occur instead of the triggering event. Here is a simple example.

Create trigger tr_NoDelete on Inventory
Instead of Delete
AS
Print 'You cannot delete Inventory Items'

Attempt to delete anything from the table. (The trigger will intercept DELETE actions before any referential integrity violations could occur)