Stored Procedures and Triggers

A two day course for application developers and database administrators. This course combines theory and hands on practice.

 

Course Objectives

On completion of this course the delegate will be able to:

1. Develop stored procedures and triggers using the appropriate SQL and SPL statements
2. Employ this knowledge to build security, business rules, and automated processes into the database server

Prerequisites

Familiarity with a programming language

Course Content

Use of triggers and stored procedures. Benefits and disadvantages
Introduction to stored procedures
The CREATE PROCEDURE syntax
DBA and non-DBA procedures
Permissions on stored procedures
Removing and re-creating stored procedures
Passing parameters to Stored Procedures
Returning values from stored procedures
Defining variables
Global variables
Implicit and explicit statement blocks
Using Stored Procedures to run SQL statements
The Stored Procedure Language statements
including FOR, SYSTEM, IF, LET, CALL, WHILE
Using cursors to manage multiple rows
For update cursors
Error handling in procedures
Handling specific errors
Raising artificial errors
Built-in functions
Debugging stored procedures
The system catalogue information for stored procedures
Introduction to triggers
Advantages and disadvantages of triggers
Types of trigger
Insert triggers, Update triggers, and Delete triggers
Applications for triggers
Data replication, audit trails, cascading deletes, disabling delete operations
Invoking stored procedures from triggers
Removing and re-creating triggers
The system catalogue information for triggers

 

Quotes from previous delegates

 

Very relaxed atmosphere, offices very pleasant. Course content comprehensive ... overall very enjoyable....

Fully enjoyed and can't wait to try out my new skills.....