How To Learn In Your Sleep
This System Not Only Gives You Back Some of Those Lost Hours it Will Also Have You Learning New Material Faster and More Accurately in Seven Days or Less – 100% Guaranteed! An in depth course of the Sleep-Learning method. Gain extra hours every day!
How To Learn In Your Sleep
Handling MySQL Events with Triggers and Procedures Using SQL – Part 10
Introduction
This is part 10 of my series, Handling MySQL Events with Triggers and Stored Procedures Using SQL. You must have read all the different parts of the series (or equivalent tutorials from elsewhere) up to this point. In this part of the series, we learn how to select data from one table row and place (insert or update) in a row of another table in MySQL. We are dealing with MySQL 5.1.
Note: If you cannot see the code or if you think anything is missing (broken link, image absent), just contact me at forchatrans@yahoo.com. That is, contact me for the slightest problem you have about what you are reading.
Project Description
We already have an Invoice table whose notation is:
   Invoice (InvoiceID, DateAndTime);
Let us create an InvoiceDetails table. An Invoice table is like a sales table. An InvoiceDetails table is like a SaleDetails table.
– Start the server and connect to it; choose the database, wholesale.
– Type and execute the following statement to create the InvoiceDetails table:
CREATE TABLE InvoiceDetails
(
   InvoiceID INTEGER NOT NULL,
   ProductID INTEGER NOT NULL
);
You should have a positive feedback. There are only two columns in the table.
So now we have an InvoiceDetails table whose notation is:
   InvoiceDetails(InvoiceID, ProductID)
For one invoice a customer may require three products. We shall write a short program of SQL statements that will insert the row information for the invoice table. Since the InvoiceID is autonumber, the program will read the number given by the DBMS and then use it to insert the InvoiceIDs (which are the same) for corresponding three products in the InvoiceDetails table.
The Code
Here is the program. Try it:
INSERT INTO Invoice () VALUES ();
SET @var = NULL;
SELECT MAX(InvoiceID)
FROM Invoice
INTO @var
INSERT INTO InvoiceDetails (InvoiceID, ProductID) VALUES (@var, 13);
INSERT INTO InvoiceDetails (InvoiceID, ProductID) VALUES (@var, 5);
INSERT INTO InvoiceDetails (InvoiceID, ProductID) VALUES (@var, 12);
Explanation of Code
The first line in the SQL code inserts a new row into the Invoice table. The parentheses are empty because for the two columns in the Invoice table, it is the DBMS that gives the values. The next statement creates the variable that will hold the most recently inserted InvoiceID in the Invoice table.
Next you have the SELECT statement. Since an InvoiceID value in the Invoice table is AUTO_INCREMENT, the most recently added InvoiceID is the maximum number in the column, everything being equal. So the SELECT statement reads this maximum number. The INTO clause of the SELECT statement copies the value read from the Invoice table to the variable of the SET statement.
The last three statements insert corresponding rows into the InvoiceDetails table. The three products are requested in one invoice, so the InvoiceID in this table for the three products has to the last one in the Invoice table. At the moment, this InvoiceID is held by the variable, @var. So this variable is in the last three statements.
The project has been accomplished.
– Stop the database, drop the connection and stop the server in one command.
I hope you are appreciating the series. We take a break here and continue in the next part.
Chrys
To arrive at any of the parts of this division, type the corresponding title below in the search box of this page and click Search (use menu if available).
Handling MySQL Events with Triggers and Procedures Using SQL – Part 1
Handling MySQL Events with Triggers and Procedures Using SQL – Part 2
Handling MySQL Events with Triggers and Procedures Using SQL – Part 3
Handling MySQL Events with Triggers and Procedures Using SQL – Part 4
Handling MySQL Events with Triggers and Procedures Using SQL – Part 5
Handling MySQL Events with Triggers and Procedures Using SQL – Part 6
Handling MySQL Events with Triggers and Procedures Using SQL – Part 7
Handling MySQL Events with Triggers and Procedures Using SQL – Part 8
Handling MySQL Events with Triggers and Procedures Using SQL – Part 9
Handling MySQL Events with Triggers and Procedures Using SQL – Part 10
Handling MySQL Events with Triggers and Procedures Using SQL – Part 11
Handling MySQL Events with Triggers and Procedures Using SQL – Part 12
Handling MySQL Events with Triggers and Procedures Using SQL – Part 13
Handling MySQL Events with Triggers and Procedures Using SQL – Part 14
Handling MySQL Events with Triggers and Procedures Using SQL – Part 15
Â
Written by Chrys
Learn to speak Korean – audio and text
Learn to speak the Korean language using audio and ebook.
Learn to speak Korean – audio and text
Learn Web Designing – Joomla Made Easy
This book is intended to expose the fundamental know-how of Joomla. Though, it may not be possible to cover everything but what is provided in this book is sufficient in making one to cover as much as one can and enough to make one an expert as long as one never stops learning and researching.
Almost every explanation is accompanied with illustrative images. The secret to this is to guide the learner to get it right quickly and easily. Once you get it right, try repeating the examples
List Price: $ 31.96
Price: