mysql lectures notes n.
Skip this Video
Download Presentation
MySQL Lectures Notes

Loading in 2 Seconds...

play fullscreen
1 / 14

MySQL Lectures Notes - PowerPoint PPT Presentation

  • Uploaded on

MySQL Lectures Notes. Stored Procedures, Triggers, Program Access Dr Lisa Ball 2008. Sources. mysql-triggers.html. Trigger Example.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'MySQL Lectures Notes' - teal

Download Now An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
mysql lectures notes

MySQL Lectures Notes

Stored Procedures, Triggers, Program Access

Dr Lisa Ball


Sources mysql-triggers.html

trigger example
Trigger Example

DELIMITER $$ -- set delimeter (allow ; inside) CREATE TRIGGER newsCounterAFTER INSERT ON NewsFOR EACH ROW BEGIN INSERT INTO NewsCount (newsItemCount) 

(SELECT count(*) FROM News);END;$$

DELIMITER ; -- reset delimeter

sample db
Sample DB

CREATE TABLE NewsCategories( catIDint not null auto_increment, 


primary key(catID));

CREATE TABLE News( newsIDint not null auto_increment, 

catIDint not null, 

title varchar(32) not null, txt blob, 

primary key(newsID));

CREATE TABLE NewsCount( newsItemCountint );

trigger example1
Trigger Example




-- Note: can reference NEW.attr on insert, update

trigger example2
Trigger Example


 (SELECT count(*) FROM News);END;


trigger statements
Trigger Statements

DROP TRIGGER newsCounter;


One trigger per event, per table

Can add procedural elements, such as IF statements

See reference 4 (databasedesign-resource) for another example

stored procedures
Stored Procedures

Why go to the trouble of extracting logic from your application, putting it into a different format, and placing it on the database server? There are several advantages to doing so. Here is a (incomplete) list of some of the most commonly sited advantages:

MySQL stored procedures can greatly cut down on the amount of traffic going back and forth over your network. (usually FASTER ingeneralthan using app program)

Stored procedures can greatly improve the security of your database server. SQL that is executed on the server is not subject to SQL injection attacks.

Stored procedures provide a way to abstract and separate data access routines from the business logic of your application.

Stored procedures allow these routines to be accessed by programs using different platforms and API's, and make your applications more portable.

From source 4 (databasedesign-resource)

stored procedures1
Stored Procedures
  • Block structured language similar to Oracle PL/SQL and IBM DB2 SQL
  • Some folks recommend using MySQL query browser to aid creation, but can be done from command line
  • Seeing what you have
stored procedures2
Stored Procedures
  • Sample DB

-- create News table, be sure to be in a 'test' DBCREATE TABLE News (NewsIDintauto_increment not null,

  Title varchar(32), 

primary key(NewsID))

stored procedures3
Stored Procedures


(SELECT * FROM News WHERE NewsID=id)) THEN   UPDATE News SET Title=title WHERE NewsID=id; ELSE   INSERT INTO News (Title) VALUES (title); END IF;END $$DELIMITER ;

To call:

CALL sprocTest(1,'Some News Title');  -- this will update recordID 1

stored procedures4
Stored Procedures
  • Using cursors
    • Let’s us loop on each row returned from a query (the result set)
    • see design-resources link (also on next slide)

stored procedure using cursors in a loop

To use:

call events(‘11/09’);

more detailed tutorial at

program db access
Program DB Access
  • First:
    • Chapter 9 Slides 18-37 from Elmasri 5th edition
  • Some sources for Java access
  • Can also access mysql with Perl, PHP, Python, Ruby