Triggers and Stored
Procedures
(Chapter 7, 11: Kroenke)
1
Today
Triggers
Stored procedures
Kroenke, Database Processing 2
Triggers
Trigger: stored program that is executed
by the DBMS whenever a specified event
occurs
Associated with a table or view
Three trigger types: BEFORE, INSTEAD
OF, and AFTER
Each type can be declared for INSERT,
UPDATE, and/or DELETE
Kroenke, Database Processing 3
Uses for Triggers
Provide complex default values
Enforce data constraints
Update views – not in MySQL
Perform referential integrity actions
Kroenke, Database Processing 4
Create Trigger – Generic Syntax
CREATE TRIGGER trigger_name
ON table_or_view_name
AFTER | BEFORE | INSTEAD OF
INSERT | UPDATE | DELETE
AS
trigger_code
Kroenke, Database Processing 5
Trigger for Enforcing a Data
Constraint – SQL Server
Arenas (ArenaID, ArenaName, City, ArenaCapacity), ArenaCapacity >= 5000
CREATE TRIGGER minseating ON Arenas /*trigger associated to Arenas*/
FOR INSERT /*executed after an insert*/
AS
DECLARE @capacity as int /*variable declarations */
SELECT @capacity = ArenaCapacity /* get values inserted */
FROM inserted
if @capacity < 5000
BEGIN
ROLLBACK /*undo the insert*/
Print 'Arena too small‘ /*message for the user*/
END
Kroenke, Database Processing 6
Trigger for Referential Integrity Actions
– pseudo-code
Kroenke, Database Processing 7
Class Exercise
Concerts (PerformerID, ArenaID, ConcertDate,
TicketPrice)
Define a trigger: if inserted price is below
25, print a message and change the ticket
price to 25.
Insert rows to test the trigger
Kroenke, Database Processing 8
Stored Procedures
A stored procedure is a program that is stored
within the database and is compiled when used
Stored procedures can receive input parameters
and they can return results
Stored procedures can be called from:
Standard languages
Scripting languages
SQL command prompt
Kroenke, Database Processing 9
Stored Procedure Advantages
Greater security as store procedures are always
stored on the database server
SQL can be optimized by the DBMS compiler
Code sharing resulting in:
Less work
Standardized processing
Specialization among developers
Kroenke, Database Processing 10
Create And Execute Stored
Procedures
CREATE PROCEDURE proc_name
AS proc_code
exec proc_name [@param1 = ]value1, …
Kroenke, Database Processing 11
Stored Procedure Example
Performers (PerformerID, PerformerName, Street, City,
State, Zip)
Procedure: Insert a performer only if same name and zip
not already in the table
Kroenke, Database Processing 12
Performers (PerformerID, PerformerName, Street, City,
State, Zip, ActivityID)
CREATE PROCEDURE performer_Insert IF @Count > 0
@ID int, BEGIN
@NewName char(20), PRINT 'Performer is already in the
@street char(20), Database'
@city char(15), RETURN
@state char(2), END
@NewZip int
AS BEGIN TRANSACTION
DECLARE @Count as int INSERT INTO
Performers(PerformerID,
PerformerName, Street, City, State,
SELECT @Count = Count(*) Zip) VALUES (@ID, @NewName,
FROM Performers @street, @city, @state, @NewZip)
WHERE PerformerName
=@NewName AND Zip = @NewZip PRINT 'Performer added to database'
COMMIT
To run: exec performer_Insert @ID = 10, @NewName = 'James Brown', @street ='Main',
@city ='Aiken', @state ='SC', @NewZip = 54322
Kroenke, Database Processing 13
Class Exercise
Add code to the previous procedure to
prevent anyone named ‘Spears’ to be
inserted into the DB. Print an error
explicative message when that happens.
Test the procedure (exec ….)
Kroenke, Database Processing 14
Triggers vs. Stored Procedures
Kroenke, Database Processing 15
Class Exercise
Students(Alpha, LName, FName, GPA)
Enroll(Alpha, CourseID, Semester, Grade)
GradeValues(LetterGrade, PointValue)
Define a trigger to update the GPA every
time the student gets a new grade, or a
grade changes
Kroenke, Database Processing 16
