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:

• Develop stored procedures and triggers using the appropriate SQL and SPL statements
• 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.....

If you would prefer a personalised course then please contact us to discuss your requirements. Oninit ® will then provide a proposal to ensure that your staff receive the training they need to optimise their performance and your system