Module 8
Functions, Procedures, and Triggers
a. Using SQL in a Programming Environment
SQL is a powerful nonprocedural language in which you submit requests to the
computer using simple commands. As in other nonprocedural languages, you can
accomplish many tasks using a single command. Although SQL and other nonprocedural
languages are well equipped to store and query data, sometimes you might need to
complete tasks that are beyond the capabilities of SQL. In such cases, you need to use a
procedural language.
A procedural language is one in which you must give the computer the systematic
process for accomplishing a task. MySQL procedural language was introduced in version
5 as an extension of SQL. MySQL procedural language allows developers to embed SQL
statements to perform complex tasks that could not be done by SQL alone. These tasks
can be saved within the database as stored procedures to be executed at anytime.
MySQL, Oracle PL/SQL, and Microsoft T-SQL to illustrate how to use SQL in a
programming environment by embedding SQL commands in another language. The
examples in this illustrate how to use embedded SQL commands to retrieve a single row,
insert new rows, update and delete existing rows, and retrieve multiple rows. In the
process, you create stored procedures that are saved and are available for use at any time.
PL/SQL, which stands for Procedural Language Structured Query Language, is a
procedural language well-integrated with SQL, which was developed by Oracle. PL/SQL,
like MySQL and others, features programming constructs such as conditions and loops
and other programming languages structures.
T-SQL, which stands for Transact-SQL, is another extension of SQL. T-SQL is
the procedural language that SQL Server uses. You can perform tasks, such as retrieving
a single row, inserting new rows, and retrieving multiple rows, using T-SQL in SQL
Server. Although the language syntax is slightly different in T-SQL when compared to
MySQL and PL/SQL, the functionality and the results are the same.
b. Using Functions
You have used aggregate functions to perform calculations based on groups of
records. SUM(BALANCE) calculates the sum of the balances on all records that satisfy
the condition in the WHERE clause. When you use a GROUP BY clause, the DBMS
calculates the sum for each record in a group.
SQL also includes functions that affect single records. Some functions affect
character data and others let you manipulate numeric data. The supported SQL functions
vary among SQL implementations. This section illustrates some common functions. For
additional information about the functions your SQL implementation supports, consult
the program’s documentation.
SQL offers a variety of functions designed to manipulate character data, allowing
users to perform various operations on strings to meet their specific requirements. One
commonly used function for manipulating character data is the UPPER function.
The UPPER function is used to convert all characters in a string to uppercase.
This can be particularly useful in scenarios where case sensitivity is not desired or where
consistency in casing is necessary for data comparison or presentation purposes.
For example, consider a database table containing customer names where the
casing of the names is inconsistent. By applying the UPPER function to the customer
names during data retrieval or comparison operations, users can ensure that the
comparison is case-insensitive and that matching records are accurately identified
regardless of the casing used in the original data.
In this, the UPPER function is applied to the customer_name column in the
customers table, resulting in the retrieval of customer names where all characters are
converted to uppercase.
The UPPER function is not only useful for data retrieval but can also be applied
in various other SQL operations. For instance, it can be used in INSERT or UPDATE
statements to ensure that data is stored consistently in uppercase format, regardless of the
casing provided by the user.
Overall, the UPPER function is a versatile tool for manipulating character data in
SQL, providing users with the ability to standardize casing, perform case-insensitive
comparisons, and achieve consistency in data presentation. Its simplicity and
effectiveness make it a valuable asset in a wide range of SQL applications.
The UPPER function displays a value in uppercase letters; for example, the
function UPPER(LAST_NAME) displays the last name Garcia as GARCIA. (Note that
the UPPER function simply displays the last name in uppercase letters; it does not change
the last name stored in the table to uppercase letters.) The item in parentheses
(LAST_NAME) is called the argument for the function. The value produced by the
function is the result of displaying all lowercase letters in the value stored in the
LAST_NAME column as uppercase letters.
You can use functions in WHERE clauses as well. For example, the condition
UPPER(LAST_NAME) = ‘GARCIA’ would be true for names like Garcia, GARCIA,
and GaRcIA, because the result of applying the UPPER function to any of these values
would result in the value GARCIA.
SQL indeed offers a plethora of functions designed to manipulate numeric data,
catering to various requirements and scenarios encountered in database management. One
such fundamental function is the ROUND function, which enables users to round
numeric values to a specified number of decimal places.
The ROUND function is particularly useful in scenarios where precise decimal
precision is required or when presenting data in a more readable format. By rounding
numeric values, users can ensure consistency in presentation and simplify complex
numerical outputs.
Furthermore, the ROUND function offers flexibility in handling different
rounding scenarios. By default, the ROUND function uses "round half up" rounding,
where values ending in 0.5 or higher are rounded up, while values below 0.5 are rounded
down. However, users can specify alternative rounding methods, such as "round half
down" or "round half towards zero," by providing additional parameters to the ROUND
function.
Additionally, the ROUND function can be combined with other SQL functions or
expressions to perform more complex calculations or formatting tasks. For example, it
can be used in conjunction with mathematical operations to round calculated values or
with string concatenation functions to format numeric outputs for display.
In summary, the ROUND function is a versatile tool for manipulating numeric
data in SQL, providing users with the ability to round values to a specified number of
decimal places, ensuring consistency in presentation, and simplifying complex numerical
outputs. Its simplicity and effectiveness make it a valuable asset in various SQL
applications, from data analysis and reporting to data manipulation and formatting tasks.
A function can have more than one argument. The ROUND function, which
rounds a numeric value to a desired number of decimal places, has two arguments. The
first argument is the value to be rounded; the second argument indicates the number of
decimal places to which to round the result. For example, ROUND(PRICE,0) rounds the
values in the PRICE column to zero decimal places (a whole number). If a price is 24.95,
the result will be 25. On the other hand, if the price is 24.25, the result will be 24 the
query and results to round values in the PRICE column to zero decimal places. The
computed column ROUND(PRICE,0) is named ROUNDED_PRICE.
Rather than rounding (using the ROUND function), you might need to truncate
(remove) everything to the right of the decimal point. To do so, use the FLOOR function,
which has only one argument. If a price is 24.95, for example, ROUND(PRICE,0) would
result in 25, whereas FLOOR(PRICE) would result in 24.
SQL provides a rich set of functions and capabilities for manipulating dates and
performing date calculations, catering to diverse requirements encountered in database
management and application development. One essential function for adding a specific
number of days, months, or years to a date is the DATE_ADD function.
The DATE_ADD function allows users to increment or decrement a given date by
a specified interval, such as days, months, or years. This flexibility enables users to
perform various date-related calculations and transformations, ranging from simple date
adjustments to complex date arithmetic operations.
The flexibility of the DATE_ADD function extends to its ability to handle
dynamic intervals based on other date-related calculations or expressions. For instance,
users can combine the DATE_ADD function with other date functions, such as NOW()
or CURDATE(), to perform calculations relative to the current date or specific reference
points.
Overall, the DATE_ADD function is a powerful tool for performing date
calculations and manipulations in SQL, offering flexibility, precision, and ease of use.
Whether it's adjusting dates for reporting purposes, calculating future or past events, or
implementing dynamic date-based logic, the DATE_ADD function provides users with
the capabilities needed to handle a wide range of date-related scenarios efficiently and
accurately.
The DATE_ADD function has two arguments. The first argument is the date to
which you want to add a specific interval to, and the second argument is the interval
value and specific component (whether days, months, or years). To add one month to the
invoice date, for example, the expression is DATE_ADD(INVOICE_DATE,
INTERVAL 1 MONTH). Note if the interval value was negative, the number of months
would be decreased by the interval value.
To add a specific number of days to a date, you do not need a function. You can
add the number of days to the invoice date. (You can also subtract dates in the same
way.) This method works in MySQL, Oracle, and SQL Server.
You can use the CURDATE() function to obtain today’s date. The command in
the figure uses CURDATE() to display today’s date and uses DATEDIFF() to determine
the number of days between the invoice date and today’s date. The values for
DAYS_PAST will vary based on the current date you execute the query.
c. Concatenating Columns
Concatenation, the process of combining two or more character columns into a
single expression, is a common requirement in SQL queries, especially when dealing
with structured data stored across multiple columns. This process allows users to merge
textual information from different sources or fields into a cohesive output, facilitating
data analysis, reporting, and presentation.
In SQL, the CONCAT() function is a versatile tool for performing concatenation
operations. It allows users to concatenate multiple character strings or columns together,
producing a single result that combines the contents of each input. The syntax of the
CONCAT() function typically involves providing multiple string expressions or column
names as arguments, separated by commas.
In this query, the CONCAT() function concatenates the first_name and last_name
columns together, separated by a space (' '), to generate a new column alias called
full_name. The result is a list of customer full names, where each entry combines the first
and last names retrieved from the respective columns.
The CONCAT() function also offers flexibility in handling NULL values. When
any of the input expressions or columns contain NULL values, the CONCAT() function
treats them as empty strings (''), ensuring that the concatenation operation proceeds
smoothly without causing errors or unexpected results.
Moreover, users can leverage the CONCAT() function to concatenate not only
column values but also additional literal strings or expressions. This capability allows for
the inclusion of custom separators, formatting elements, or textual labels within the
concatenated output, enhancing the readability and usability of the resulting data.
In this example, the CONCAT() function combines the 'Customer ID: ',
customer_id, ', Name: ', first_name, and last_name components to create a comprehensive
customer information string, including both identifiers and names.
Furthermore, SQL offers alternative concatenation methods, such as the
concatenation operator (||) or the CONCAT_WS() function, which allows users to specify
a custom separator for concatenating values. However, the CONCAT() function remains
a popular choice for its simplicity, readability, and compatibility across different database
management systems.
Overall, the CONCAT() function is a valuable tool for performing concatenation
operations in SQL queries, enabling users to merge character data from multiple columns
or sources into unified expressions for analysis, reporting, and presentation purposes. Its
versatility, ease of use, and compatibility make it a fundamental component of SQL-
based data manipulation and retrieval workflows.
To concatenate the FIRST_NAME and LAST_NAME columns, use the
CONCAT() function with character columns or strings separated by comma like the
following expression: CONCAT(FIRST_NAME, ‘ ‘. LAST_NAME).
When dealing with character data in SQL databases, it's essential to consider the
width of columns and how data is stored within them. In scenarios where the length of
the provided data (such as a first name) is shorter than the specified width of the column
(as determined by the number of characters specified in the CREATE TABLE
command), SQL employs a mechanism to ensure that the stored value aligns with the
defined width.
To achieve this, SQL inserts extra spaces, known as padding, to fill the remaining
space within the column. This process ensures that the stored value occupies the entire
width of the column, maintaining consistency in data presentation and facilitating
efficient data retrieval and manipulation operations.
Padding is particularly relevant in fixed-length character fields, where each value
is allocated a predetermined number of characters. By padding shorter values with spaces
to match the defined width, SQL ensures uniformity in the structure of the stored data,
simplifying data management tasks and enhancing data integrity.
For example, suppose a database table includes a column named "first_name"
with a specified width of 20 characters. If a particular record contains a first name that is
only 10 characters long, SQL will automatically insert 10 extra spaces to pad the value to
the full width of the column.
In this example, even though the first name "John" is only four characters long,
SQL will pad it with 16 extra spaces to ensure that the stored value occupies the full
width of the "first_name" column (20 characters).
Padding ensures that data is consistently formatted and aligned within database
tables, facilitating efficient data storage, retrieval, and presentation. However, it's
important to consider the implications of padding, particularly in terms of storage space
and data presentation, when designing database schemas and applications.
In SQL, when dealing with concatenated expressions involving character data,
such as combining the FIRST_NAME and LAST_NAME columns, it's crucial to manage
the resulting output effectively. As mentioned, if the FIRST_NAME column is 12
characters in length and the provided first name is "Joey," followed by the last name
"Smith," SQL will pad the first name with extra spaces to fill the designated width of the
column.
The resulting concatenated expression may appear as "Joey Smith," with
additional spaces following the first name value. To address this issue and remove the
extra spaces, SQL provides the RTRIM (right trim) function. RTRIM is a string function
used to remove trailing spaces from the right side of a string.
Using the RTRIM function in conjunction with the concatenated expression
allows users to eliminate any excess spaces following the first name value, ensuring a
cleaner and more visually appealing output.
In this example, the RTRIM function is applied to the first_name column to
remove any trailing spaces, followed by concatenation with the last_name column using
the concatenation operator (||). The result is a concatenated expression representing the
full name without any extra spaces following the first name.
By leveraging the RTRIM function, users can ensure that concatenated
expressions involving character data are presented accurately and aesthetically, without
the presence of unnecessary spaces or padding. This enhances the readability and
usability of query results, improving the overall user experience and facilitating effective
data analysis and reporting.
Furthermore, SQL offers additional string functions and operators that users can
utilize to manipulate character data and format query results according to their specific
requirements. These functions include LTRIM (left trim) for removing leading spaces,
TRIM for removing spaces from both ends of a string, and various other string
manipulation functions for tasks such as substring extraction, case conversion, and
pattern matching.
In summary, the RTRIM function is a valuable tool for cleaning up concatenated
expressions and removing trailing spaces from character data in SQL queries. By
incorporating RTRIM into query logic, users can enhance the presentation of query
results and ensure that data is displayed accurately and professionally.
When utilizing the RTRIM function in SQL to process column values, the
function operates by removing any trailing spaces that may have been inserted at the end
of the original value. This action ensures that the output displayed by SQL accurately
reflects the true content of the column without any unwanted whitespace.
This functionality is particularly beneficial in scenarios where data integrity and
presentation are critical. By applying RTRIM to column values, SQL can effectively
clean up extraneous spaces that might have been introduced during data entry,
manipulation, or storage processes. This ensures that query results and reports provide
users with accurate and visually appealing information.
For instance, consider a database table storing product descriptions. If certain
descriptions have been padded with trailing spaces to meet a predefined character limit,
the RTRIM function can be employed to strip away these excess spaces, presenting the
descriptions in their original form without any unnecessary whitespace.
Furthermore, the removal of trailing spaces by RTRIM enhances the usability of
query results by eliminating potential inconsistencies in data presentation. Users can
confidently rely on SQL to display column values accurately, regardless of any
whitespace padding that may have been applied.
Moreover, the application of RTRIM demonstrates SQL's capability to
manipulate and format data dynamically during query execution. By incorporating
functions like RTRIM into SQL queries, users can tailor the presentation of data to meet
specific requirements and preferences, enhancing the overall effectiveness of data
analysis and reporting tasks.
In summary, when the RTRIM function is applied to column values in SQL, it
serves to preserve data integrity by removing any trailing spaces added to the original
values. This functionality ensures that query results accurately reflect the content of the
database and enhances the usability and professionalism of data presentation in SQL-
based applications and reports.
When dealing with a column of CHAR data type, it's important to be mindful of
potential padding issues, as CHAR columns store fixed-length character data and may
pad values with spaces to reach the specified width. To ensure that trailing spaces are
removed before concatenating values, we can utilize the RTRIM function.
Suppose we have a table named "customers" with columns "customer_id,"
"first_name" (CHAR data type), and "last_name." Let's consider an example for
Customer 125, where we want to concatenate the first and last names, ensuring that any
trailing spaces in the "first_name" column are removed.
Assuming the "first_name" column for Customer 125 contains the value "Joey "
(padded with spaces to reach the defined width), the RTRIM function will remove the
trailing spaces, resulting in "Joey." The concatenation operator (||) is then used to
combine "Joey" with a single space (' '), followed by the "last_name" value "Smith,"
resulting in the desired full name "Joey Smith."
By utilizing the RTRIM function in conjunction with concatenation, we ensure
that any extra spaces added to the "first_name" column are removed before combining it
with the "last_name," resulting in a clean and accurate full name representation for
Customer 125.
This approach ensures that the concatenated output accurately reflects the data
stored in the database, providing users with a consistent and professional presentation of
customer names, even when dealing with CHAR columns that may include trailing
spaces.
d. Stored Procedures Using MySQL
In a client/server system, the database is stored on a computer called the server
and users access the database through clients. A client is a computer that is connected to a
network and has access through the server to the database. Every time a user executes a
query, the DBMS must determine the best way to process the query and provide the
results. For example, the DBMS must determine which indexes are available and whether
it can use those indexes to make the processing of the query more efficient.
When you anticipate running a particular query often, you can improve overall
performance by saving the query in a file called a stored procedure. The stored procedure
is placed on the server. The DBMS compiles the stored procedure (translating it into
machine code) and creates an execution plan, which is the most efficient way of
obtaining the results. From that point on, users execute the compiled, optimized code in
the stored procedure.
Another reason for saving a query as a stored procedure, even when you are not
working in a client/server system, is convenience. Rather than retyping the entire query
each time you need it, you can use the stored procedure. For example, suppose you
frequently execute a query that selects a customer ID with a given number and then
displays the concatenation of the first name and last name of the customer. Instead of
typing the query each time you want to display a customer’s name, you can save the
query in a stored procedure. You would then only need to run the stored procedure when
you want to display a sales rep’s name. In MySQL, you create stored procedures using a
language called MySQL. You can create and save the procedures as script files.
Procedure to find the name of the customer full name whose number is stored in
the I_CUST_ID argument. Because the restriction involves the primary key, the query
produces only one row of output. The command is stored in a script file and is displayed
in the Script Editor. To create the procedure, you would run the script file. Assuming that
the script file does not contain any errors, MySQL would then create the procedure and it
would be available for use.
The CREATE PROCEDURE command in MySQL is a powerful feature that
allows users to define and execute custom routines or procedures within the database.
These procedures can contain one or more SQL statements and are stored in the database
for repeated execution. However, in scenarios where a procedure with the same name
already exists in the database, it's essential to drop or delete the existing procedure before
re-creating it to avoid conflicts or errors.
To drop a procedure in MySQL, the DROP PROCEDURE command is used. This
command removes the specified procedure from the database, freeing up its name for
reuse or modification. When dropping a procedure, it's important to ensure that any
dependencies or references to the procedure are also addressed to prevent unintended
consequences.
By executing this command, MySQL will remove the
GET_CUSTOMER_NAME procedure from the database, if it exists. This ensures that
the procedure name is available for re-creation or modification without causing conflicts
with the existing procedure definition.
It's important to note that dropping a procedure permanently deletes it from the
database, so caution should be exercised to avoid inadvertently removing essential
procedures or routines. Additionally, dropping a procedure may require appropriate
privileges or permissions depending on the database user's access level.
In summary, the DROP PROCEDURE command in MySQL is used to remove
existing procedures from the database, allowing users to manage and update stored
routines effectively. When re-creating procedures with the same name, it's good practice
to first drop any existing procedures to avoid conflicts and ensure smooth execution of
database operations.
Typically, MySQL Workbench uses a semicolon “;” as a delimiter to SQL
commands and execute statements delimited by semicolon separately. When creating a
procedure, it is necessary to write several statements using a semicolon to end each
statement within the procedure. For this reason, the DELIMITER keyword is used to
define characters that tell MySQL that the statements submitted within the delimiter is
treated as one. First line in DELIMITER keyword followed by double slashes “//” as a
delimiter. In addition, the END keyword is delimited by the same double slashes
indicating statements between the double slashes is one statement.
The CREATE PROCEDURE command contains a single argument, I_CUST_ID.
The word IN following the single argument name indicates that I_CUST_ID is used for
input. That is, the user must enter a value for I_CUST_ID to use the procedure. Other
possibilities are OUT, which indicates that the procedure sets a value for the argument,
and INOUT, which indicates that the user enters a value that the procedure can later
change.
The procedural code, which contains the commands that specify the procedure’s
function, appears between the BEGIN and END commands. The DECLARE statement
within the procedure code defines a variable to store values to be used at a later stage.
When defining variable names in MySQL, the name may consist of alphanumeric, dollar
signs, underscores, and number signs, but cannot exceed 64 characters. In addition, part
of the declaration of a variable you must assign a data type, just as you do in the SQL
CREATE TABLE command, assigning variables I_CUST_ID as CHAR and
V_FULL_NAME as VARCHAR data type.
The procedural code contains the SQL command to select the last name and first
name of the sales rep whose number is stored in I_CUST_ID. The SQL command uses
the INTO clause to place the result of the concatenated FIRST_NAME and
LAST_NAME. The next command is SELECT V_FULL_NAME to display the stored
value of the variable V_FULL_NAME. Notice that a semicolon ends each variable
declaration, command except the word END followed by double slashes indicating end of
the code.
e. Error Handling
Handling conditions that can arise when accessing the database is a crucial aspect
of developing robust and reliable procedures in MySQL. In scenarios like the one
described, where a user enters a customer ID and expects to retrieve the corresponding
customer's name, it's essential to anticipate and address various potential conditions or
exceptions that may occur during the execution of the procedure.
Implementing error handling mechanisms allows procedures to gracefully handle
unexpected conditions, such as invalid input parameters or database errors. In MySQL,
error handling can be accomplished using constructs like the DECLARE CONTINUE
HANDLER and DECLARE EXIT HANDLER statements to capture and process errors
or exceptions that occur during procedure execution.
Validating user input parameters before accessing the database helps prevent
errors and ensure data integrity. Procedures can perform validation checks, such as
verifying that the provided customer ID exists in the database or meets certain criteria,
before proceeding with the query execution.
Handling exceptions that may arise during database operations, such as record not
found or data retrieval failures, is essential for providing informative feedback to users
and preventing application crashes. Procedures can use conditional logic and error-
checking mechanisms to detect and handle exceptions gracefully, returning meaningful
error messages or fallback values as needed.
Managing database transactions effectively ensures data consistency and
reliability, especially in multi-step procedures involving multiple database operations.
Procedures can utilize transaction control statements like BEGIN, COMMIT, and
ROLLBACK to define transaction boundaries and handle transactional errors or rollbacks
appropriately.
Logging procedure activities and database interactions helps diagnose issues,
track execution flow, and audit user actions. Procedures can incorporate logging
mechanisms, such as writing to log tables or using MySQL's logging features, to record
relevant information about procedure executions, error conditions, and database
interactions for analysis and troubleshooting purposes.
Designing procedures with graceful degradation in mind ensures that they can
handle unexpected conditions or temporary failures in a resilient manner. Procedures can
implement fallback mechanisms, default values, or alternative paths to maintain
functionality and provide a satisfactory user experience even under adverse conditions.
By incorporating these strategies into the development of procedures, MySQL
applications can better handle conditions that may arise when accessing the database,
enhancing reliability, performance, and user satisfaction. Additionally, thorough testing
and validation of procedures under various scenarios help identify and address potential
issues before deployment, ensuring smooth and error-free operation in production
environments.
When a user enters an invalid customer ID, it typically results in MySQL not
finding any corresponding record in the database. In such cases, when attempting to
retrieve data associated with the provided customer ID, MySQL returns NULL values for
the fields that would have been populated if the record had been found.
In the context of the scenario described, where the procedure
GET_CUSTOMER_NAME is expected to display the last name corresponding to the
entered customer ID, encountering an invalid customer ID would lead to the procedure
returning a NULL value for the last name.
Handling situations where an invalid customer ID is entered requires careful
consideration to ensure that the procedure behaves predictably and provides informative
feedback to the user.
Before attempting to retrieve data based on the provided customer ID, the
procedure should validate the input to ensure that it conforms to expected criteria. This
validation may include checking if the customer ID exists in the database or meets certain
formatting requirements.
Implement error handling mechanisms within the procedure to handle cases where
the customer ID is invalid or the corresponding record is not found in the database. This
could involve capturing errors or exceptions that occur during the data retrieval process
and providing appropriate error messages or fallback values.
Define fallback values or default behavior to handle cases where the requested
data cannot be retrieved due to an invalid customer ID. For example, instead of returning
NULL, the procedure could return a predefined message indicating that the customer ID
is invalid or prompt the user to enter a valid customer ID.
Provide clear and informative feedback to the user when an invalid customer ID is
entered. This could involve displaying error messages or prompts on the user interface to
guide the user in entering a valid customer ID or informing them of the reason for the
failure to retrieve the requested data.
Log information about failed attempts to retrieve data based on invalid customer
IDs for monitoring and troubleshooting purposes. This can help identify patterns of
invalid input or potential issues with data integrity in the database.
By implementing these strategies, MySQL procedures can effectively handle
situations where an invalid customer ID is entered, ensuring that the application behaves
robustly and provides a positive user experience even in error scenarios. Additionally,
thorough testing and validation of the procedure under various conditions help identify
and address potential issues before deployment.
You can include the “declare an exception” handle to handle processing an invalid
customer ID. When a user enters a customer ID that does not match any customer in the
CUSTOMER table, NOT FOUND condition is raised and the procedure displays the
message proceeded by SELECT statement in the same line. The message ‘No customer
with this ID was found: ’ followed by the invalid customer ID.
The GET_CUSTOMER_NAME procedure handles an error that results when a
user enters an invalid customer ID. There are other types of errors that procedures must
handle, depending on the processing required. For example, a user might enter a
commission rate in a procedure to find the name of the sales rep who has that commission
rate. When the user enters the rate 0.04, the procedure displays an 1172 (stands for too
many rows) error because Susan, Donna, and Daniel have this same commission rate—
the procedure finds three rows instead of one. You can manage this error by trapping the
error code 1172 in the error handling declaration statement, respectively.
f. Using Update Procedures
Learning to use SQL commands for updating data is a fundamental skill in
database management, and the ability to apply these commands within procedures
extends the capability to automate and streamline data maintenance tasks. An update
procedure, as the name suggests, is a type of stored procedure in SQL that is designed to
perform updates on database records based on predefined criteria or conditions.
By encapsulating update logic within a procedure, you can ensure consistent and
standardized updates across the database. This helps maintain data integrity by enforcing
business rules and validation checks before modifying records.
Update procedures can handle complex update operations that involve multiple
tables or conditional logic. This allows for more sophisticated data transformations and
manipulations, such as updating related records or recalculating derived values based on
certain criteria.
Update procedures can be used to manage transactions effectively, ensuring that
updates are performed atomically and reliably. By encapsulating multiple update
statements within a single transaction, you can maintain data consistency and rollback
changes if any errors occur during the update process.
Procedures can accept parameters as input, allowing for dynamic updates based
on user input or external conditions. This enables the reuse of the same procedure with
different input values, improving code maintainability and reducing redundancy.
Update procedures can include error handling logic to gracefully handle
exceptions or unexpected conditions that may arise during the update process. This helps
prevent data corruption and ensures that updates are completed successfully even in the
event of errors.
Update procedures can be augmented with audit trail functionality to track
changes made to the database. By logging information about updates, such as the user
who initiated the update and the timestamp of the change, you can maintain a
comprehensive history of data modifications for auditing and compliance purposes.
Procedures can be optimized for performance by leveraging SQL optimization
techniques, such as indexing and query optimization. This helps minimize the impact of
update operations on database performance and ensures efficient data processing.
In summary, update procedures are a powerful tool in SQL for automating data
maintenance tasks, enforcing data integrity, and managing complex update operations.
By encapsulating update logic within procedures, you can improve code maintainability,
enhance data consistency, and streamline database management processes. With proper
design and implementation, update procedures can significantly contribute to the
efficiency and reliability of database applications.
This procedure is similar to the procedures used in previous examples with two
main differences: It uses an UPDATE command instead of a SELECT command, and
there are two arguments, I_CUST_ID and I_NEW_NAME. The I_CUST_ID argument
stores the customer ID to be updated and the I_NEW_NAME argument stores the new
value for the customer last name.
Indeed, just like update procedures, delete procedures are a vital component in
database management, providing a systematic way to remove unwanted records from
tables based on predefined criteria. These procedures offer numerous benefits and
functionalities similar to update procedures, making them a valuable tool in database
maintenance and data manipulation tasks.
Delete procedures help maintain data integrity by enforcing consistent deletion
rules and validation checks. By encapsulating delete logic within a procedure, you can
ensure that only authorized users can delete records and that deletion operations adhere to
business rules and constraints.
Delete procedures allow for conditional deletion of records based on specified
criteria. This enables you to selectively remove records that meet certain conditions, such
as obsolete data, expired records, or duplicates, while preserving relevant information in
the database.
Like update procedures, delete procedures support transaction management,
enabling you to perform deletion operations atomically and reliably. By grouping delete
statements within a transaction, you can ensure that either all deletion operations succeed
or none of them are applied, maintaining data consistency and integrity.
Delete procedures can accept parameters as input, allowing for dynamic deletion
based on user input or external factors. This enables the reuse of the same procedure with
different input values, enhancing code reusability and flexibility.
Delete procedures can incorporate error handling mechanisms to handle
exceptions or unexpected conditions that may arise during the deletion process. This
helps prevent data corruption and ensures that deletion operations are completed
successfully even in the event of errors.
Delete procedures can be augmented with audit trail functionality to log
information about deleted records. By recording details such as the user who initiated the
deletion, the timestamp of the deletion, and the affected records, you can maintain a
comprehensive audit trail for tracking changes and ensuring accountability.
Delete procedures can be optimized for performance using SQL optimization
techniques such as indexing and query optimization. This helps minimize the impact of
deletion operations on database performance and ensures efficient data processing.
In summary, delete procedures play a crucial role in database management by
providing a systematic and controlled approach to removing records from tables. By
encapsulating deletion logic within procedures, you can ensure data integrity, enforce
business rules, and manage deletion operations efficiently and reliably. With proper
design and implementation, delete procedures contribute to the overall efficiency,
reliability, and maintainability of database applications.
If you attempt to delete the invoice in the INVOICES table first, referential
integrity will prevent the deletion because matching rows would still exist in the
INVOICE_LINE table, so it is a good idea to delete the orders from the INVOICE_LINE
table first. The procedure to delete an invoice and its related invoice lines appears. This
procedure contains two DELETE commands. The first command deletes all invoice lines
in the INVOICE_LINE table on which the invoice number matches the value stored in
the I_INVOICE_NUM argument. The second command deletes the order in the
INVOICES table whose invoice number matches the value stored in the I_INVOICE_
NUM argument.
g. Selecting Multiple Rows with a Procedure
The procedures you have seen so far include commands that retrieve individual
rows. You can use an UPDATE or a DELETE command in MySQL to update or delete
multiple rows. The commands are executed and the updates or deletions occur. Then the
procedure can move on to the next task.
What happens when a SELECT command in a procedure retrieves multiple rows?
For example, suppose the SELECT command retrieves the ID and name of each customer
represented by the sales rep whose ID is stored in I_REP_ID. There is a problem—
MySQL can process only one record at a time, but this SQL command retrieves more
than one row. Whose ID and name are placed in I_CUST_ID and I_CUST_NAME when
the command retrieves more than one customer row? Should you make I_CUST_ID and
I_CUST_NAME arrays capable of holding multiple rows and, if so, what should be the
size of these arrays? Fortunately, you can solve this problem by using a cursor.
A cursor is a pointer to a row in the collection of rows retrieved by an SQL
command. (This is not the same cursor that you see on your computer screen.) The cursor
advances one row at a time to provide sequential, one-record-at-a-time access to the
retrieved rows so MySQL can process the rows. By using a cursor, MySQL can process
the set of retrieved rows as though they were records in a sequential file.
Declaring and utilizing a cursor in SQL procedures is a powerful technique for
iterating over result sets and performing row-by-row processing. Cursors provide a means
to fetch and manipulate individual rows returned by a query, offering flexibility and
control over data traversal within procedural code.
In the context of a SQL procedure, the first step in using a cursor involves
declaring the cursor and specifying the associated query within the declaration section of
the procedure. Let's delve deeper into this process using an example where the cursor is
named CUSTGROUP.
The DECLARE CURSOR statement is used to declare a cursor named
CUSTGROUP within the procedure. This statement initializes the cursor and associates it
with a specific query that defines the result set to be processed.
The associated query specifies the data to be retrieved by the cursor. In this case,
the query selects the customer_id and customer_name columns from the customers table,
filtering rows based on the condition that the customer_group is 'Group A'. This query
defines the result set that the cursor will traverse.
1. The cursor is assigned the name CUSTGROUP, which can be referenced
within the procedure to manipulate the result set returned by the query.
Once the cursor is declared, it can be used within the procedural code to fetch
rows from the result set, process each row individually, and perform desired operations.
Cursors provide methods for navigating through the result set, fetching rows, and
accessing column values for each fetched row.
After declaring the cursor, subsequent steps typically involve opening the cursor,
fetching rows one at a time, performing processing or operations on each fetched row,
and finally closing the cursor when processing is complete.
It's important to note that while cursors offer flexibility in data traversal, they can
also impact performance, especially when dealing with large result sets. Therefore,
cursors should be used judiciously, and alternative set-based operations should be
considered whenever possible for improved efficiency.
In summary, declaring a cursor within a SQL procedure involves specifying the
associated query and initializing the cursor to traverse the resulting rows. Cursors provide
a valuable mechanism for row-by-row processing and are commonly used in procedural
code to perform iterative tasks and data manipulation operations.
This command does not cause the query to be executed at this time; it only
declares a cursor named CUSTGROUP and associates the cursor with the indicated
query. Using a cursor in a procedure involves three commands: OPEN, FETCH, and
CLOSE. The OPEN command opens the cursor and causes the query to be executed,
making the results available to the procedure. Executing a FETCH command advances
the cursor to the next row in the set of rows retrieved by the query and places the contents
of the row in the indicated variables. Finally, the CLOSE command closes a cursor and
deactivates it. Data retrieved by the execution of the query is no longer available. The
cursor could be opened again later and processing could begin again.
Indeed, the OPEN, FETCH, and CLOSE commands used in processing a cursor
in SQL procedures share similarities with the OPEN, READ, and CLOSE commands
used in processing a sequential file in traditional programming languages. Both sets of
commands are employed for iterative data processing, but they operate within different
contexts and environments.
In SQL procedures, the OPEN command is used to initialize a cursor, making it
ready to fetch rows from the result set defined by the associated query. Similarly, in
traditional programming, the OPEN command is used to initialize a file or data stream for
reading.
Once the cursor or file is opened, both SQL and traditional programming
environments utilize a FETCH or READ command to retrieve data sequentially from the
result set or file, respectively. This iterative process allows data to be processed row by
row or record by record.
During the iteration phase, data retrieved by the FETCH or READ command can
be processed or manipulated as needed. In SQL procedures, this typically involves
performing calculations, condition checks, or other operations on the fetched rows.
Similarly, in traditional programming, data processing tasks such as parsing, validation,
or transformation are performed on the data read from the file.
The iteration continues until all rows have been fetched from the result set in SQL
or until the end of the file is reached in traditional programming. At this point, the cursor
is closed using the CLOSE command in SQL, or the file is closed using the CLOSE
command in traditional programming.
Both SQL and traditional programming environments support error handling
mechanisms to deal with exceptions or unexpected conditions that may arise during data
processing. This includes handling errors related to cursor operations or file I/O
operations, respectively.
Proper resource management is essential in both scenarios. In SQL, cursors
should be explicitly opened and closed to ensure efficient use of database resources.
Similarly, in traditional programming, files should be properly opened and closed to
prevent resource leaks and ensure data integrity.
While the concepts and commands used in cursor processing in SQL procedures
and file processing in traditional programming are similar, there are also notable
differences, particularly in terms of the underlying data structures, environments, and
programming paradigms. Understanding these similarities and differences is crucial for
developers transitioning between SQL and traditional programming environments and for
effectively leveraging the respective capabilities of each platform.
Prior to opening the cursor, there are no rows available to be fetched. This is
indicated by the absence of data in the CUSTGROUP portion of the figure. The right side
of the figure illustrates the variables into which the data is placed (I_CUST_ID and
I_CUST_NAME) and the value DONE set to FALSE. (DONE is a variable to be set to
TRUE when an exception is raised indicating the cursor has no more rows.) Once the
cursor has been opened and all the records have been fetched, the value of DONE is set to
TRUE by an exception declared in the procedure. Procedures using the cursor can use
this value to indicate when the fetching of rows is complete.
In the figure, assume that I_REP_ID is set to 15 before the OPEN command is
executed; there are now three rows available to be fetched. No rows have yet been
fetched, as indicated by the absence of values in I_CUST_NUM and I_CUST_NAME.
DONE is still FALSE. The cursor is positioned at the first row; that is, the next FETCH
command causes the contents of the first row to be placed in the indicated variables.
Note that the INTO clause is associated with the FETCH command itself and not
with the query used in the cursor definition. The execution of this query could produce
multiple rows. The execution of the FETCH command produces only a single row, so it
is appropriate that the FETCH command causes data to be placed in the indicated
variables.
The result of four FETCH commands. The first three fetches are successful. In
each case, the data from the appropriate row in the cursor is placed in the indicated
variables and DONE is still FALSE. The fourth FETCH command is different, however,
because there is no more data to fetch. In this case, the exception no more rows is raised
and the contents of the variables are left untouched and DONE is set to TRUE.
The declaration portion contains the CUSTGROUP cursor definition. The
procedural portion begins with the command to open the CUSTGROUP cursor. The
statements between the LOOP and END LOOP commands create a loop that begins by
fetching the next row from the cursor and placing the results in I_CUST_ID and
I_CUST_NAME. The LEAVE command exits the loop if the condition DONE is tested
to be TRUE. If the condition is not true, the SELECT I_CUST_ID and I_CUST_NAME
are displayed.
The query formulation that defined the cursor in was straightforward. Any SQL
query is legitimate in a cursor definition. In fact, the more complicated the requirements
for retrieval, the more numerous the benefits derived by the programmer who uses
embedded SQL.
The retrieval requirements are substantial. Beyond coding the preceding cursor
definition, the programmer doesn’t need to worry about the mechanics of obtaining the
necessary data or placing it in the right order, because this happens automatically when
the cursor is opened. To the programmer, it seems as if a sequential file already exists
that contains the correct data, sorted in the right order.
The coding in the procedure is greatly simplified. Normally in a program, the
programmer must determine the most efficient way to access the data. In a program or
procedure using embedded SQL, the optimizer determines the best way to access the
data. The programmer isn’t concerned with the best way to retrieve the data. In addition,
when an underlying structure changes, the optimizer determines the best way to execute
the query with the new structure. The program or procedure does not have to change at
all. When the database structure changes in such a way that the necessary information is
still obtainable using a different query, the only change required in the program or
procedure is the cursor definition. The procedural code is not affected.
h. Using PL/SQL in Oracle
In PL/SQL procedural language the CREATE PROCEDURE command, like
MySQL, tells Oracle to store the procedure in the database. By including the optional OR
REPLACE clause in the CREATE PROCEDURE command, you can use the command
to modify an existing procedure. If you omit the OR REPLACE clause, you would need
to drop the procedure and then re-create it in order to change the procedure later.
Similar to MySQL, the word IN, OUT, and INOUT behave in the same way in
Oracle. Variable names in PL/SQL must start with a letter and can contain letters, dollar
signs, underscores, and number signs, but cannot exceed 30 characters. All declared
variables must be assigned a data type. You can ensure that a variable has the same data
type as a particular column in a table by using the %TYPE attribute. To do so, you
include the name of the table, followed by a period and the name of the column, and then
%TYPE. When you use %TYPE, you do not enter a data type because the variable is
automatically assigned the same type as the corresponding column is written as
CUSTOMER.CUST_ID%TYPE.
The first line of the CREATE PROCEDURE command ends with the word AS
and is followed by the commands in the procedure. The commands on lines 2 and 3
declare the local variables the procedure requires. In Oracle, lines 2 and 3 create two
variables named V_LAST_NAME and V_FIRST_NAME. Both variables are assigned
data types using %TYPE.
In Oracle, the procedural code begins with the SQL command to select the first
name and last name of the sales rep whose number is stored in I_CUST_ID. Similar to
MySQL, the SQL command uses the INTO clause to place the results in the V_FIRST_
NAME and V_LAST_NAME variables. The next command uses the
DBMS_OUTPUT.PUT_ LINE procedure to display the concatenation of the
V_FIRST_NAME and V_LAST_NAME variables. Notice that a semicolon ends each
variable declaration, command, and the word END. The slash (/) at the end of the
procedure appears on its own line. In some Oracle environments, the slash is optional. A
good practice is to include the slash even when it’s not necessary so your procedure
always works correctly.
DBMS_OUTPUT is a package that contains multiple procedures, including
PUT_LINE. The SQL Commands page automatically displays the output produced by
DBMS_ OUTPUT. In the SQL Command Line environment, you first have to execute a
SET SERVEROUTPUT ON command to display the output.
To call (or use) the procedure from the SQL Commands page, type the word
BEGIN, followed by the name of the procedure including the desired value for the
argument in parentheses, followed by the word END, a semicolon, and a slash on a
separate line. To use the GET_CUST _NAME procedure to find the name of customer ID
125.
Similar to MySQL you can handle conditions that arise when accessing the
database. You can include the EXCEPTION clause shown in Oracle to handle processing
an invalid customer ID. In order to handle an exception error no data is found, you may
use the NO_DATA_FOUND condition on line 11 as true. When the
NO_DATA_FOUND condition is true, the procedure displays the “No customer with this
ID: ” message followed by the invalid customer ID.
Another common exception in PL/SQL is TOO_MANY_ROWS caused when the
SELECT statement returns more than one row. You can manage this error by writing a
WHEN clause that contains a TOO_MANY_ROWS condition, following the
EXCEPTION clause in the procedure. You can write both WHEN clauses in the same
procedure or in separate procedures. When adding both WHEN clauses to the same
procedure, however, the EXCEPTION clause appears only once.
A complete procedure using a cursor. The declaration portion contains the
CUSTGROUP cursor definition. The procedural portion begins with the command to
open the CUSTGROUP cursor. The statements between the LOOP and END LOOP
commands create a loop that begins by fetching the next row from the cursor and placing
the results in V_INVOICE_NUM, V_INVOICE_DATE, V_CUST_ID, V_REP_ID,
V_REP_LAST, V_REP_FIRST. The EXIT command tests the condition CUSTGROUP
%NOTFOUND. If the condition is true, the loop is terminated. If the condition is not
true, the DBMS_OUTPUT. PUT_LINE commands display the contents of variables.
i. Using T-SQL in SQL Server
SQL Server uses an extended version of SQL called T-SQL (Transact-SQL). You
can use T-SQL to create stored procedures and use cursors. The reasons for creating and
using stored procedures and cursors are identical to those discussed for PL/SQL. Only the
command syntax is different.
The CREATE PROCEDURE command in the stored procedure causes SQL
Server to create a procedure named usp_GET_CUST_NAME. The usp_prefix identifies
the procedure as a user-stored procedure. Although using the prefix is optional, it is an
easy way to differentiate user-stored procedures from SQL Server system-stored
procedures. The argument for this procedure is @custid. In T-SQL, you must assign a
data type to parameters. All arguments start with the at (@) sign. Arguments should have
the same data type and length as the particular column in a table that they represent. In
the CUSTOMER table, CUST_ID was defined with a CHAR data type and a length of 2.
The CREATE PROCEDURE command ends with the word AS followed by the SELECT
command that comprises the procedure.
Creating a stored procedure in T-SQL (Transact-SQL) for changing the name of a
customer involves defining the procedure with parameters to accept input values for the
customer ID and the new name. T-SQL, the procedural extension of Microsoft SQL
Server, provides a robust set of features for writing stored procedures to encapsulate
complex logic and operations.
Stored procedures offer several advantages in database management, including
improved code organization, enhanced security, and better performance through reduced
network traffic. By encapsulating data manipulation logic within stored procedures, T-
SQL developers can promote code reusability, maintainability, and scalability in SQL
Server environments.
To create a stored procedure in T-SQL for deleting an invoice number from both
the INVOICE_LINE table and the INVOICES table, we can define a procedure with a
parameter to accept the invoice number as input. T-SQL provides powerful capabilities
for defining stored procedures to encapsulate complex data manipulation tasks.
Executing this statement will invoke the DeleteInvoice stored procedure, deleting
the specified invoice number from both the INVOICE_LINE and INVOICES tables
within the same transaction, ensuring data consistency.
Cursors serve the same purpose in T-SQL as they do in MySQL and PL/SQL and
work exactly the same way. You need to declare a cursor, open a cursor, fetch rows from
a cursor, and close a cursor. The only difference is in the command syntax.
The procedure uses one argument, @repid. It also uses two variables and each
variable must be declared using a DECLARE statement. You also declare the cursor by
giving it a name, describing its properties, and associating it with a SELECT statement.
The cursor property, READ_ONLY, means that the cursor is used for retrieval purposes
only. The OPEN, FETCH, and CLOSE commands perform the same tasks in T-SQL as
they do in MySQL. The OPEN command opens the cursor and causes the query to be
executed. The FETCH command advances the cursor to the next row and places the
contents of the row in the indicated variables. The CLOSE command closes a cursor and
the DEALLOCATE command flushes and the association to the cursor is removed. The
DEALLOCATE command is not necessary, but it does enable the user to use the same
cursor name with another procedure.
The WHILE loop repeats until the value of the system variable
@@FETCH_STATUS is not zero. The PRINT command outputs the values stored in
@custid and @custname variables.
j. Using a Trigger
A trigger, within the context of database management systems, serves as a pivotal
mechanism that allows for the automatic execution of predefined procedures or actions in
response to specific database operations. These operations typically include INSERT,
UPDATE, or DELETE commands, among others. Triggers are an indispensable
component of database functionality, offering a means to enforce data integrity,
implement business logic, and ensure consistency within the database environment.
When a trigger is defined for a particular table or database object, it effectively
acts as a sentinel, monitoring the specified operations and responding accordingly. This
automated responsiveness is crucial in various scenarios, ranging from maintaining
referential integrity to enforcing complex business rules and auditing changes within the
database.
Triggers can be classified into different types based on when they are executed:
BEFORE triggers, which execute before the associated operation takes place; AFTER
triggers, which execute after the operation completes; and INSTEAD OF triggers, which
execute in place of the original operation, providing an alternative action or behavior.
Moreover, triggers can encapsulate a wide array of actions, such as updating
related tables, performing calculations, sending notifications, logging changes, or
enforcing access controls. Their versatility enables database administrators and
developers to implement intricate logic and enforce specific constraints seamlessly.
One notable advantage of triggers is their ability to centralize and streamline
complex database operations, reducing the need for manual intervention and minimizing
the risk of human error. By automating routine tasks and enforcing consistency across the
database, triggers contribute to overall system reliability and data quality.
However, it's essential to exercise caution when designing and implementing
triggers, as poorly crafted triggers can introduce performance bottlenecks, introduce
unintended side effects, or lead to maintenance challenges. Therefore, careful
consideration of the trigger's logic, execution timing, and potential impact on system
performance is crucial during the development and deployment phases.
In summary, triggers play a fundamental role in modern database systems,
providing a mechanism for automating actions in response to database operations. Their
versatility, coupled with their ability to enforce data integrity and business rules, makes
them indispensable tools for ensuring the reliability, consistency, and functionality of
database environments.
Stored procedures and triggers are both integral components of database systems,
serving distinct but complementary purposes in managing data and enforcing business
logic. While stored procedures are primarily invoked in response to explicit user requests,
triggers operate autonomously, responding to predefined events or commands that initiate
database operations.
Stored procedures encapsulate a set of SQL statements and procedural logic,
designed to perform specific tasks or operations within the database environment. These
procedures are typically invoked by application programs or users directly, often in
response to user interactions or application events. Their execution is explicitly triggered
by the calling application or user action, and they operate within the scope of the user's
session or transaction.
On the other hand, triggers are special types of stored procedures that are bound
to specific tables or database objects and automatically executed in response to
predefined events or commands affecting those objects. These events include INSERT,
UPDATE, DELETE, or even DDL (Data Definition Language) statements such as
ALTER TABLE. Triggers operate transparently to the user or application, providing a
seamless means of enforcing data integrity constraints, implementing business rules, or
auditing changes within the database.
One notable distinction between stored procedures and triggers lies in their
execution context and timing. Stored procedures execute within the context of a user
session or transaction, initiated explicitly by the user or application, and their execution is
subject to user privilege and transaction isolation levels. In contrast, triggers operate
within the context of the database itself, responding to events triggered by DML (Data
Manipulation Language) or DDL statements regardless of the user session, connection, or
transaction boundaries.
Moreover, triggers offer a powerful mechanism for implementing complex
database behaviors and enforcing consistency across multiple tables or database objects.
They can perform a wide range of actions, including data validation, cascading updates or
deletes, logging changes, sending notifications, or even invoking external procedures or
services. Their automatic execution nature makes them particularly useful for enforcing
business rules and ensuring data integrity without relying on explicit user actions.
However, it's essential to exercise caution when designing and implementing
triggers, as they can introduce overhead, complexity, and potential performance
implications if not carefully crafted. Additionally, excessive or poorly designed triggers
may lead to maintenance challenges, debugging difficulties, or unintended side effects,
necessitating thorough testing and optimization during development.
In summary, while stored procedures and triggers serve distinct roles in database
management, understanding their differences in execution context, timing, and
functionality is essential for leveraging them effectively to meet the diverse requirements
of database applications. Whether executing procedural logic in response to user requests
or autonomously enforcing data integrity constraints, both stored procedures and triggers
play vital roles in ensuring the reliability, consistency, and functionality of database
systems.
The examples in this section assume there is a new column named ON_ORDER
in the ITEM table. This column represents the number of units of an item currently on
order. For example, if there are two separate invoice lines for an item and the number
ordered on one invoice line is 3 and the number ordered on the other invoice line is 2, the
ON_ ORDER value for that item will be 5. Adding, changing, or deleting invoice lines
affects the value in the ON_ORDER column for the item. To ensure that the value is
updated appropriately, you can use a trigger.
If you created the ADD_INVOICE_LINE trigger in MySQL, the SQL command
in the trigger would be executed when a user adds an invoice line. The trigger must
update the ON_ORDER value for the corresponding item to reflect the invoice line. For
example, if the value in the ON_ORDER column for item AD72 is 3 and the user adds an
invoice line on which the item number is AD72 and the number of units ordered is 2, then
5 units of item AD72 will be on order. When a record is added to the ORDER_ LINE
table, the ADD_INVOICE_LINE trigger updates the ITEM table by adding the number
of units ordered on the invoice line to the previous value in the ON_ORDER column.
The first line indicates that the command is creating a trigger named
ADD_INVOICE_ LINE. The third line indicates that this trigger is executed after an
invoice line is inserted, and that the SQL command is to occur for each row that is added.
Like stored procedures, the SQL command is enclosed between the words BEGIN and
END. In this case, the SQL command is an UPDATE command. The command uses the
NEW qualifier, which refers to the row that is added to the INVOICE_LINE table. If an
invoice line is added on which the item number is AD72 and the number ordered is 2, for
example, NEW.ITEM_ID will be AD72 and NEW.QUANTITY will be 2.
The UPDATE_INVOICE_LINE trigger in MySQL is executed when a user
attempts to update an invoice line. There are two differences between the
UPDATE_INVOICE_LINE trigger and the ADD_INVOICE_LINE trigger. First, the
third line of the UPDATE_INVOICE_LINE trigger indicates that this trigger is executed
after an UPDATE of an invoice line rather than an INSERT. Second, the computation to
update the ON_ORDER column includes both NEW.QUANTITY and
OLD.QUANTITY. As with the ADD_INVOICE_LINE trigger, NEW.QUANTITY
refers to the new value. In an UPDATE command, however, there is also an old value,
which is the value before the update takes place. If an update changes the value for
QUANTITY from 2 to 3, OLD.QUANTITY is 2 and NEW.QUANTITY is 3. Adding
NEW.QUANTITY and subtracting OLD.QUANTITY results in a net change of an
increase of 1. (The net change could also be negative, in which case the ON_ORDER
value decreases.)
The DELETE_INVOICE_LINE trigger in MySQL performs a function similar to
the other two triggers. When an invoice line is deleted, the ON_ORDER value for the
corresponding item is updated by subtracting OLD.QUANTITY from the current
ON_ORDER value. (In a delete operation, there is no NEW.QUANTITY.)
