Cos 346 Day 14 Agenda



Download 57,17 Kb.
Date conversion18.10.2017
Size57,17 Kb.

COS 346

  • Day 14

Agenda

  • Questions?
  • Assignment 5 not corrected
    • Will get done this weekend
  • Assignment 6 Posted
    • Due March 27
  • Assignment 7 will be posted this weekend
    • Chap 7,8,9 & 10
    • One assignment per week
  • Quiz 2 on Apr 3
    • DP Chap 7 & 8
    • SQL Chap 3-10
    • M/C and Short Essay
  • Capstone Project Proposals Over Due
    • Capstone progress report due March 27
  • More on SQL & SQL Server
    • Today we look at chap 9&10 in SQL Text
    • Additional Functions, Stored Procedures and Triggers.

Computer Faculty Candidate Presentation

  • 11:00 a.m. on March 24    
  • Campus-wide Presentation-Nadeau Hall Teleconference Room114A
    • Topic for presentation:
    • 30 min on the Benefits of Object Oriented Programming
    • First year computer students with little to no programming experience.
    • The presentation should be generic and not based on any one programming language itself but on the object oriented programming paradigm.  The basic concepts covered should include abstractions, classes, methods, properties, message passing, inheritance, interfaces, encapsulation, and polymorphism.

Chapter 9: Additional Functions

  • SQL for SQL Server Bijoy Bordoloi and Douglas Bock

Objectives

  • Use string and text functions to manipulate character and text data.
  • Use mathematical functions to manipulate numeric data.
  • Use conversion functions to convert data from one data type to another.
  • Use date/time functions to manipulate date/time data.

General Notation

  • Functions are formally defined using the following general notation.
  • FUNCTION( argument1 [,optional_argument2]
  • [, optional_argument3], … )
  • The function name is in CAPITAL letters.
  • Argument parameters may be filled by either a string of characters enclosed in single-quote marks, a numeric value, an expression, or a column name.
  • Some functions require more than one argument and some arguments are optional.

String and Text Functions

  • This category of functions manipulates character strings and text/image data.
  • Numbers may be treated as character strings if they are not manipulated mathematically.
  • Type of Data to be Stored Example Values
  • Customer Street Address 100 S. Main St.
  • Telephone Number (618) 555-1212
  • Customer Name Bijoy Bordoloi
  • Social Security Number 999-99-9999
  • Product Number 12396

ASCII and CHAR Functions

  • The ASCII function returns the ASCII code value as an INT numeric value for the left-most character in a character string.
  • The character string must be data type CHAR or VARCHAR.
  • The CHAR function returns the ASCII character for an argument of data type INT.
  • SELECT ASCII('Bijoy') "ASCII",
  • CHAR(98) "Character"
  • ASCII Character
  • ----------- ---------
  • 66 b

UNICODE Function

  • This works like the ASCII function but it returns an INT value according to the Unicode standard.
  • Unicode strings must be of data type NCHAR or NVARCHAR.
  • SELECT UNICODE('$10,000') "Value of $ Sign", UNICODE('₤') "Value of ₤ Sign"
  • Value of $ Sign Value of ₤ Sign
  • --------------- ---------------
  • 36 163
  • http://www.lookuptables.com/

STR Function

  • This converts numeric data to a character string and requires a FLOAT data type with a decimal point as a required argument.
  • Two optional arguments: (1) specify the length of the returned character string and (2) number of digits to the right of the decimal point.
  • SELECT STR(12345.6789,8,2) "Rounded Up",
  • STR(12345.6744,8,2) "Rounded Down",
  • STR(12345.6789,4,2) "Length Too Short",
  • STR(12345.6789,6) "No Decimal Value";
  • Rounded Up Rounded Down
  • ---------- ------------
  • 12345.68 12345.67
  • Length Too Short No Decimal Value
  • ---------------- ----------------
  • **** 12346

Another STR Function Example

  • SELECT STR(emp_salary,7,0) "Salary As String", emp_salary "Salary as Money"
  • FROM employee;
  • Salary As String Salary as Money
  • ---------------- ---------------------
  • 30000 30000.0000
  • 25000 25000.0000
  • 38000 38000.0000
  • more rows will be displayed…

LEFT and RIGHT Functions

  • These are string extraction functions—they extract substrings from strings.
  • The LEFT returns the left part of a character string for the specified number of characters. The RIGHT returns the right part of a character string.
  • The character string argument may be any data type that can be implicitly converted to VARCHAR or NVARCHAR, but cannot be TEXT or NTEXT (too big < 2GB).
  • The integer value must be a positive value that specifies the number of characters to extract (return) from the character string.

LEFT and RIGHT Functions--Example

  • SELECT emp_last_name "Full Name",
  • LEFT(emp_last_name, 5) "First 5",
  • RIGHT(emp_last_name, 5) "Last 5"
  • FROM employee;
  • Full Name First 5 Last 5
  • -------------------- ------- ------
  • Bock Bock Bock
  • Amin Amin Amin
  • Joshi Joshi Joshi
  • Zhu Zhu Zhu
  • Joyner Joyne oyner
  • Bordoloi Bordo doloi
  • Markis Marki arkis
  • Prescott Presc scott

LEN Function

  • This returns a numeric value equivalent to the number of characters in a specified character string. Trailing blanks are ignored.
  • It is useful for determining the amount of space to be allocated for an output column in a report.
  • SELECT DISTINCT emp_city "City",
  • LEN(emp_city) "Length" FROM employee;
  • City Length
  • ------------------------- -----------
  • Collinsville 12
  • Edwardsville 12
  • Marina 6
  • Monterey 8

LTRIM and RTRIM Functions

  • These functions trim leading and trailing characters from CHAR data columns.
  • CHAR data columns are automatically blank-padded when values are not sufficiently large to fill up all of the column space—concatenating the columns causes a display with too much blank space.
  • SELECT drug_name+drug_unit "Drug",
  • RTRIM(drug_name)+' '+RTRIM(drug_unit)
  • "Concatenated" FROM drug_table;
  • Drug Concatenated
  • ------------------ -----------------
  • Aspirin 25 mg Aspirin 25 mg
  • Toprol 0.05 mg Toprol 0.05 mg
  • Ibuprofen 800 mg Ibuprofen 800 mg

UPPER and LOWER Functions

  • These alter the appearance of information displayed by converting string data to either upper or lower case characters.
    • SELECT LOWER(emp_gender) "Gender",
    • UPPER(emp_last_name) "Last Name"
    • FROM employee;
    • Gender Last Name
    • ------ -------------------------
    • m BOCK
    • m AMIN
    • m JOSHI
    • more rows will be displayed…

Combining and Embedding Functions

  • The State column data is stored as 2-character capitalized abbreviations for state names.
    • SELECT LTRIM(emp_last_name) "Last Name", UPPER(LEFT(emp_state,1)) + LOWER(RIGHT(emp_state,1)) "State"
    • FROM employee;
    • Last Name State
    • ------------------------- -----
    • Bock Mo
    • Amin Ca
    • Joshi Il

CHARINDEX Function

  • This returns the INT value for the starting position of a substring in a string—a kind of search function to confirm the existence of a substring.
  • The first string argument is the substring to be found in the second character string argument.
  • The second character string argument is usually expressed as a column from a table.
  • The start position argument is optional and can be used to specify a character position starting point for the search.
  • A value of 0 (zero) is returned if the substring is not found.

CHARINDEX Function—Example

  • This lists all employees that live on High St. by searching the emp_address column (substring is not equal to zero).
  • SELECT RTRIM(emp_last_name) + ', ' + RTRIM(emp_first_name) "Employee",
  • emp_address "Address"
  • FROM employee
  • WHERE CHARINDEX('High St', emp_address) != 0;
  • Employee Address
  • ---------------- ---------------------
  • Markis, Marcia High St. #14

REPLACE Function

  • This scans a character string and replaces a character substring with another specified character substring. Here we replace the acronym ER with the words “Emergency Room.”
  • SELECT REPLACE(note_comment, 'ER', 'Emergency room') "Note Comment“
  • FROM patient p INNER JOIN patient_note pn
  • ON p.pat_id = pn.pat_id
  • WHERE CHARINDEX('admitted from ER', note_comment)
  • != 0;
  • Patient Note Comment
  • --------------- --------------------------------------
  • Howard, Ronald Patient admitted from Emergency room
  • More rows will be displayed...

SUBSTRING Function

  • A very powerful function to extract a substring from a string—used to display portions of large text character strings.
  • Works with character, binary, text, or image data.
  • There are three required arguments—argument #1 is the character string, argument #2 is an INT value specifying the start position to extract, and argument #3 specifies the number of characters to be extracted.

SUBSTRING Function—Example #1

  • This example extracts the last four digits of each employee’s social security number.
  • SELECT RTRIM(emp_last_name) + ', ' +
  • RTRIM(emp_first_name) "Employee",
  • SUBSTRING(emp_ssn,6,4) "Last 4 SSN"
  • FROM employee
  • WHERE emp_dpt_number = 3;
  • Employee Last 4 SSN
  • ----------------------- ----------
  • Amin, Hyder 2222
  • Joyner, Suzanne 5555
  • Markis, Marcia 7777

SUBSTRING Function—Example #2

  • This formats the SSN by inserting dash symbols.
  • SELECT emp_last_name+', '+emp_first_name "Employee ", SUBSTRING(emp_ssn,1,3)+'-'+SUBSTRING(emp_ssn,4,2)+'-'+
  • SUBSTRING(emp_ssn,6,4) "SSN"
  • FROM employee
  • WHERE emp_dpt_number = 3;
  • Employee SSN
  • ----------------------- -----------
  • Amin, Hyder 999-22-2222
  • Joyner, Suzanne 999-55-5555

Mathematical Functions

  • This category of functions manipulates values stored as numeric data.
  • Aggregate numeric functions were covered in Chapter 5.
  • This chapter focuses on functions that act on single numeric values and those that perform special mathematical manipulations.

Single-Value Functions

  • These functions can be combined with the arithmetic operator symbols (+ - * / %) to develop complex expressions.
  • Numeric functions accept numeric arguments – column names that are defined as numeric data types and expressions – they return numeric values – the type of data returned varies.

Transcendental Functions

  • These include single value functions: ACOS, ASIN, ATAN, ATAN2, COS, EXP, LOG, LOG10, SIN, and TAN.
  • We will not focus on most of these as they are rarely used in business except for the financial and marketing research areas.
  • SELECT COS(0.5) "COS", EXP(1) "EXP",
  • LOG(0.5) "LOG", LOG10(0.5) "LOG10";
  • COS EXP LOG LOG10
  • -------- --------- ---------- ----------
  • 0.877582 2.7182818 -0.6931472 -0.3010300
  • more precision is displayed with SQL Query Analyzer

ISNULL Function

  • This function allows you to deal with situations where data columns have NULL values.
  • The function works with numeric, character, date, and other data types. If the check_expression argument is NULL, the ISNULL function returns the replacement_value argument.
  • The query on the next slide shows a result requested by a senior project manager by listing a value of 0.0 where the work_hours column is NULL.

ISNULL Function—Example

  • SELECT work_emp_ssn "SSN", work_pro_number "Project", ISNULL(work_hours, 0) "Hours"
  • FROM assignment
  • WHERE work_pro_number IN (1,20);
  • SSN Project Hours
  • --------- ------- -------
  • 999111111 1 31.4
  • 999444444 1 .0
  • 999444444 20 11.8
  • 999555555 20 14.8
  • 999666666 20 .0
  • 999888888 1 21.0

ABS Function

  • This returns the absolute value as a measure of magnitude, and works for a numeric value or expression.
  • This example shows the difference in work reported from the 20 hours established as the desired standard.
  • SELECT work_emp_ssn "SSN", work_pro_number "Project #", work_hours "Worked", ABS(work_hours - 20) "Difference"
  • FROM assignment
  • WHERE ABS(work_hours - 20) >= 10
  • ORDER BY ABS(work_hours - 20);
  • SSN Project # Worked Difference
  • --------- --------- --------- ----------
  • 999887777 30 30.8 10.8
  • 999111111 1 31.4 11.4

POWER, SQRT, SQUARE and ROUND

  • POWER raises a numeric argument to a specified positive exponent.
  • SQUARE squares FLOAT numeric values – this is equivalent to POWER(number, 0.5).
  • SQRT takes the square root of a FLOAT value.
  • SELECT POWER(25.0, 3.0) "Cubed",
  • POWER(25.0, 0.5) "0.5 Power",
  • SQRT(25) "Square Root", SQUARE(25.0) "Squared";
  • Cubed 0.5 Power Square Root Squared
  • --------- ---------- ----------- --------
  • 15625.0 5.0 5.0 625.0

ROUND Function

  • This rounds numeric values to specific levels of mathematical precision – two arguments are needed: the value to round and a value representing the precision.
  • SELECT work_emp_ssn "SSN", work_hours "Hours",
  • ROUND(work_hours,0) "Rounded to 0",
  • ROUND(work_hours,-1) "Rounded to 10"
  • FROM assignment ORDER BY work_emp_ssn;
  • SSN Hours Rounded to 0 Rounded to 10
  • --------- ----- ------------ -------------
  • 999111111 31.4 31.0 30.0
  • 999111111 8.5 9.0 10.0
  • 999222222 34.5 35.0 30.0

Conversion Functions

  • These convert values of one data type to another data type.
  • This is termed explicit casting or conversion.
  • SQL Server also supports implicit casting, which is the automatic conversion among data types.

CONVERT Function

  • This supports any SQL Server data type. You can use CONVERT in both SELECT and WHERE clauses.
  • Argument 1 is the data type to convert to and argument 2 is the value to be converted. Both are required. Argument 3 is optional and specifies a display style for output formatting through the use of style codes.
  • SELECT CONVERT(CHAR(12), emp_last_name+', '+emp_first_name) "Name“ FROM employee;
  • Name
  • ------------
  • Bock, Dougla
  • Amin, Hyder
  • Joshi, Dines

CONVERT Function—Example 2

  • This displays employee last name and first initial.
  • SELECT emp_last_name + ',
  • '+CONVERT(CHAR(1), emp_first_name)
  • +'.' "Name"
  • FROM employee;
  • Name
  • -----------------------------
  • Bock, D.
  • Amin, H.
  • Joshi, D.

CONVERT Function—Example 3

  • This converts the emp_date_of_birth column (a DATETIME data type) to CHAR for display formatted using the style parameter code of 107 (page 234).
  • SELECT CONVERT(CHAR(15),
  • RTRIM(emp_last_name) + ', ' +
  • RTRIM(emp_first_name)) "Employee",
  • CONVERT(CHAR(15), emp_date_of_birth,
  • 107) "Birthday"
  • FROM employee;
  • Employee Birthday
  • --------------- ---------------
  • Bock, Douglas Dec 05, 1950
  • Amin, Hyder Mar 29, 1969
  • Joshi, Dinesh Sep 15, 1972

CAST Function

  • This works identical to the CONVERT except CAST does not support the style argument. The syntax is different and uses the keyword AS to specify the data type.
  • SELECT CAST(emp_last_name+',
  • '+emp_first_name AS CHAR(12)) "Name"
  • FROM employee;
  • Name
  • ------------
  • Bock, Dougla
  • Amin, Hyder
  • Joshi, Dines

DATE Functions

  • The DATETIME and SMALLDATETIME data types store both date and time information.
  • The DATETIME data type stores more accuracy (to 3.33 milliseconds) and a wider range of dates.
  • The DATE functions enable mathematical manipulation of dates.

GETDATE and GETUTCDATE Functions

  • GETDATE returns the current operating system date and time while GETUTCDATE returns this value using the Universal Time Coordinate (Greenwich Mean Time).
  • SELECT GETDATE() "Current Date and Time";
  • Current Date and Time
  • -------------------------
  • 2003-02-06 19:22:08.153

DatePart Table

  • DatePart is an argument parameter used in several date functions. This specifies the part of a date to return by a function. The argument also has accepted abbreviations.
  • Datepart Abbrev. Datepart Abbrev.
  • Year yy, yyyy Day dd, d
  • Quarter qq, q Hour hh
  • Month mm, m Minute mi, n
  • Day of year dy, y Second ss, s
  • Week wk, ww Millisecond ms
  • Day of week* dw
  • *not accepted for datepart – use the abbreviation.

Date Arithmetic – the DATEADD Function

  • DATEADD takes three arguments – the DatePart argument, the value to add (as a number), and the value that is being modified (usually a column value or expression that is a date data type).
  • SELECT CONVERT(CHAR(15), RTRIM(emp_last_name) + ', ' +RTRIM(emp_first_name)) "Employee",
  • DATEADD(year, 65, emp_date_of_birth) "Date Turns 65"
  • FROM employee;
  • Employee Date Turns 65
  • --------------- -----------------------
  • Bock, Douglas 2015-12-05 00:00:00.000
  • Amin, Hyder 2034-03-29 00:00:00.000

Date Arithmetic – the DATEDIFF Function

  • DATEDIFF performs date subtraction by subtracting two date column values to produce the number of years, quarters, months, and so on. between two dates. Use DatePart as a parameter to specify the type of difference to take.
  • The first parameter is the DatePart argument.
  • The second parameter is the start_date.
  • The third parameter is the end_date.

DATEDIFF Function—Example

  • This displays the number of months the manager of department 3 has worked in his position.
  • SELECT dpt_mgrssn "SSN", emp_last_name
  • "Last Name", DATEDIFF(mm, dpt_mgr_start_date, GETDATE()) "Number of Months"
  • FROM department d INNER JOIN employee e
  • ON d.dpt_mgrssn = e.emp_ssn
  • WHERE dpt_no = 3;
  • SSN Last Name Number of Months
  • --------- ------------- ----------------
  • 999555555 Joyner 25
  • Your answer will vary depending on when you execute the query.

DATENAME, DAY, MONTH, and YEAR Functions

  • DATENAME requires the DatePart argument and the date to be displayed as a name. DAY, MONTH, and YEAR require a date argument and return an INT.
  • SELECT CAST(emp_last_name AS CHAR(12)) "Last Name", CAST(DATENAME(mm, emp_date_of_birth)
  • AS CHAR(12)) "Month Born",
  • DAY(emp_date_of_birth) "Day Born",
  • MONTH(emp_date_of_birth) "Month",
  • YEAR(emp_date_of_birth) "Year"
  • FROM employee;
  • Last Name Month Born Day Born Month Year
  • --------- ---------- -------- ----- ----
  • Bock December 5 12 1950

Summary

  • Additional functions add power to SQL queries.
  • The Character functions manipulate character data. Two important functions are CHARINDEX for searching strings and SUBSTRING for extracting substrings.
  • Mathematical Functions manipulate numeric data types and include the transcendental functions for financial and marketing research business applications.
  • The ISNULL function substitutes values where NULLs are stored.
  • CONVERT and CAST convert data from one data type to another.
  • Date functions generate system dates and enable date arithmetic as well as the extraction and display of date data.

Chapter 10: Stored Procedures and Triggers

  • SQL for SQL Server Bijoy Bordoloi and Douglas Bock

Objectives

  • Learn the advantages of stored procedures.
  • Learn about the permissions required to create stored procedures.
  • Create and execute stored procedures.
  • Write stored procedures with multiple parameters.
  • Learn to avoid stored procedure parameter errors.
  • Drop stored procedures.
  • Learn how triggers work.
  • Learn the different types of triggers.
  • Write program code to create AFTER and INSTEAD OF triggers.
  • Define the order of trigger execution for tables with multiple triggers.

Stored Procedures

  • A stored procedure is a kind of computer program to process tasks that are repetitive in nature—meaning these computerized tasks are conducted over and over during the course of business.
  • Example: a procedure to process salary increases for employees of an organization.

Advantages of Stored Procedures

  • The ability to automate repetitive tasks.
  • The ability to call a stored procedure from an application program written in another programming language such as Visual Basic.NET and Visual C++.
  • Efficiency—SQL Server will generate and maintain (store) an execution plan for a stored procedure—thus, the stored procedure will execute very efficiently.
  • Security—you can grant permission to execute stored procedures that modify database objects such as tables without granting explicit permission to modify the database objects directly.

Permission to Create Procedures

  • Permission to Create Procedures
  • Procedures are created by executing the CREATE PROCEDURE statement.
  • The statement can be abbreviated to CREATE PROC.
  • A Database Administrator must grant you the permission to execute the CREATE PROCEDURE statement.

CREATE PROCEDURE Syntax

  • CREATE PROCEDURE [owner.]procedure_name [(@parameter1 datatype1
  • [=DEFAULT], [{@parameter2 datatype2 [= DEFAULT], . . .})]
  • AS {INSERT | UPDATE | DELELTE} table_name
  • {code to execute for the procedure}
  • The owner of a procedure is the system user account which is assigned ownership through this particular option. Often ownership is assigned to a system user who works as a DBA.
  • Procedure parameters are optional (@parameter1, @parameter2, …) enclosed within parentheses of specified data types (datatype1, datatype2, …).
  • Parameter names and data types are paired and multiple parameter and data type pairs are separated by commas. Parameters may or may not be assigned default values.
  • Parameters are used to pass values to the stored procedure—they can also pass values back to a calling procedure.

Stored Procedure Example #1—Pay Raise

  • This procedure receives the percent value of the raise to be assigned through the parameter named @percent_raise.
  • The default value is 3%, any percentage raise can be passed to the stored procedure.
  • CREATE PROCEDURE pay_raise (@percent_raise DECIMAL(3,1)=3.0) AS UPDATE employee
  • SET emp_salary = emp_salary +
  • (emp_salary * @percent_raise/100);
  • This is an UPDATE procedure that will update each employee record by increasing the salary through the SET clause.

Creating Stored Procedure #1 Using Query Analyzer

Executing Stored Procedure #1

  • Stored procedures can be executed with the SQL Query Analyzer through use of the EXECUTE statement. This statement may be abbreviated as EXEC. The simplified, general syntax of the EXEC statement is:
  • EXEC procedure_name [(@parameter1=value1
  • {, @parameter2=value2, …})]
  • Here is an example of executing the stored procedure named pay_raise.
  • EXEC pay_raise 7.5
  • (8 row(s) affected)

Results from Executing Stored Procedure #1

  • SQL Example 10.4 executes a SELECT statement to show the new salary levels. The procedure raised all employee salaries by 7.5%.
  • SELECT emp_ssn "SSN", emp_last_name "Last Name",
  • CONVERT (CHAR (10), emp_salary, 1) "Salary"
  • FROM employee
  • ORDER BY emp_ssn;
  • SSN Last Name Salary
  • --------- ------------------------- ----------
  • 999111111 Bock 32,250.00
  • 999222222 Amin 26,875.00
  • 999333333 Joshi 40,850.00
  • more rows will display …

Stored Procedure Example #2—Individual Raise

  • As an alternative to a stored procedure that provides all employees with a raise, this stored procedure is designed to enable the application of percentage raises for individual employees.
  • This procedure takes two input parameter values: (1) the employee social security number (@emp_ssn), and (2) percent of raise (@percent_raise).
  • The SSN is CHAR data while the percent raise is DECIMAL data.

Stored Procedure Example #2—Individual Raise Contd. Procedure Code

  • The procedure displays the information on the employee before and after the raise.
  • CREATE PROCEDURE individual_raise (@emp_ssn CHAR(9),
  • @percent_raise DECIMAL(3,1))
  • AS SELECT emp_ssn "SSN", emp_last_name "Last Name",
  • CONVERT (CHAR (10), emp_salary, 1) "Old Salary"
  • FROM employee WHERE emp_ssn = @emp_ssn
  • UPDATE employee
  • SET emp_salary = emp_salary +
  • (emp_salary * @percent_raise/100)
  • WHERE emp_ssn = @emp_ssn
  • SELECT emp_ssn "SSN", emp_last_name "Last Name",
  • CONVERT (CHAR (10), emp_salary, 1) "New Salary"
  • FROM employee WHERE emp_ssn = @emp_ssn;

Stored Procedure Example #2—Individual Raise Contd. Output Results

  • EXEC individual_raise @emp_ssn=999555555, @percent_raise=10.0
  • SSN Last Name Old Salary
  • --------- ------------------------- ----------
  • 999555555 Joyner 46,225.00
  • (1 row(s) affected)
  • (1 row(s) affected)
  • SSN Last Name New Salary
  • --------- ------------------------- ----------
  • 999555555 Joyner 50,847.50
  • (1 row(s) affected)

The DEFAULT Parameter Option

  • A default parameter value is the value used by a stored procedure when a parameter value is not supplied.
  • The default is optional and when specified, is set as is shown here.
  • CREATE PROCEDURE pay_raise
  • (@percent_raise DECIMAL(3,1)=3.0)
  • This EXEC causes the percent raise to be 3.0% by default.
  • EXEC pay_raise

Stored Procedure Parameter Errors

  • Errors arise for several reasons such as:
  • Attempting to pass parameter values of the incorrect data type
  • Failing to pass required parameter values so that the procedure cannot execute properly.
  • SQL Example 10.8 shows an attempt to execute the individual_raise procedure without specifying the required percent amount of the raise.
  • EXEC individual_raise @emp_ssn=999666666
  • Server: Msg 201, Level 16, State 3, Procedure individual_raise, Line 0 Procedure 'individual_raise' expects parameter '@percent_raise', which was not supplied.

Stored Procedure Parameter Errors Contd.

  • Here is an attempt to execute the individual_raise procedure by specifying the social security number and percentage of raise in reverse order without specifying the names of the parameters
  • SQL Server generates an error message indicating that the data type int could not be converted to decimal (those data types expected by the procedure).
  • EXEC individual_raise 7.5, 999666666
  • Server: Msg 8114, Level 16, State 1, Procedure individual_raise, Line 0
  • Error converting data type int to decimal.

Dropping Stored Procedures

  • Stored procedures are dropped with the DROP PROCEDURE statement.
  • To drop a stored procedure, you must be the owner of the procedure, or a DBA with the db_owner or sysadmin role permissions.
  • DROP PROCEDURE pay_raise;
  • The command(s) completed successfully.

Triggers

  • Triggers are program code objects in a database that are invoked whenever a specific action occurs to a table.
  • With early database management systems, computer scientists referred to the firing of a trigger; hence the adoption and continued use of the term trigger.
  • Triggers can be invoked by any action that inserts rows into a table, updates existing table rows, or deletes table rows.

Triggers (cont.)

  • Modern DBMS products like SQL Server use triggers to enforce a new type of constraint termed a PROCEDURAL INTEGRITY constraint.
  • This means that triggers enforce what business managers refer to as business rules.
  • For example, the Company may have a policy (business rule) that employees cannot receive a raise that exceeds 20% of their current salary level.
  • You have permission to create a trigger for a specific table if you are the owner of the table. A system user with an account that is designated as a data definition language administrator or a database owner can also create a trigger for a table.

Trigger Syntax

  • Triggers have four components: (1) the trigger name, (2) the table or view name to which the trigger is assigned, (3) the timing of the trigger action and associated DML action, and (4) the program code to be executed.
  • The simplified, general syntax of the CREATE TRIGGER statement is shown here:
  • CREATE TRIGGER trigger_name ON
  • {table_name | view_name }
  • {FOR | AFTER | INSTEAD OF }
  • {[INSERT,] [UPDATE,] [DELETE]}
  • AS {batch code | IF UPDATE
  • (column_name)}

Trigger Syntax (Cont.)

  • Trigger_name is the name of the trigger as a database object.
  • The table or view for a trigger is specified with the ON clause.
  • FOR, AFTER, and INSTEAD OF options define when a trigger acts.
    • FOR and AFTER clauses both specify that a trigger fires after the event that triggers the firing – AFTER and FOR are synonymous.
    • INSTEAD OF specifies that a trigger should execute instead of the event that would normally activate (fire) the trigger. AFTER triggers are only supported on tables, not views.
  • INSERT, UPDATE, and DELETE statements specify which DML event will cause a trigger to fire – DML options can be combined, but you cannot specify a DELETE statement option for a trigger with an IF UPDATE clause.
  • The AS clause is used to specify whether the trigger executes: procedural and nonprocedural T-SQL statements or an IF UPDATE clause.

Trigger Example #1—Updating Employee Salary

  • This example uses an audit trail table to store information about changes made to employee salary data.
  • Audit_Employee Table Structure
  • Column Name Column Data Type and Size
  • emp_ssn CHAR(9)
  • old_salary MONEY
  • new_salary MONEY
  • system_user_name CHAR(20)
  • datetime_changed DATETIME

The CREATE TABLE Statement for the Audit_Employee Table

  • CREATE TABLE audit_employee (
    • emp_ssn CHAR(9),
    • old_salary MONEY,
    • new_salary MONEY,
    • system_user_name CHAR(20),
    • datetime_changed DATETIME,
  • CONSTRAINT pk_audit_employee
    • PRIMARY KEY (emp_ssn, datetime_changed) );

Creating/Testing the Update Salary Trigger

  • CREATE TRIGGER update_salary
  • ON employee AFTER UPDATE
  • AS IF UPDATE(emp_salary)
  • BEGIN
  • DECLARE @emp_ssn CHAR(9)
  • DECLARE @old_salary MONEY
  • DECLARE @new_salary MONEY
  • SELECT @old_salary = (SELECT emp_salary
  • FROM deleted)
  • SELECT @new_salary = (SELECT emp_salary
  • FROM inserted)
  • SELECT @emp_ssn = (SELECT emp_ssn
  • FROM inserted)
  • INSERT INTO audit_employee VALUES (@emp_ssn, @old_salary, @new_salary, USER_NAME(), GETDATE())
  • END

Understanding the Update Salary Trigger

  • The trigger fires when an UPDATE occurs to a row of the employee table as specified by the ON clause.
  • The AS clause object is the IF UPDATE specification for the emp_salary column of the employee table.
  • The BEGIN and END statements denote the trigger code.
    • Three variables are declared that store the employee social security number, old salary, and new salary (@emp_ssn, @old_salary, @new_salary).
    • The data types for the variables match the associated columns in the employee table.
    • SELECT statements store values to the three variables by selecting from two virtual tables in SQL Server named deleted and inserted (these two tables are described later).
    • An INSERT statement inserts a row into the audit_employee table.
    • The USER_NAME( ) and GETDATE( ) functions extract the system user name and date of the modification from system tables.

Testing the Update Salary Trigger

  • Test the trigger by executing the individual_raise procedure created earlier. Assign employee Bock with social security number 999-11-1111 a 5% raise.
    • EXEC individual_raise @emp_ssn=999111111, @percent_raise=5.0
    • SSN Last Name Old Salary
    • --------- ---------------------- ----------
    • 999111111 Bock 33,217.50
    • (1 row(s) affected)
    • (1 row(s) affected)
    • (1 row(s) affected)
    • SSN Last Name New Salary
    • --------- ---------------------- ----------
    • 999111111 Bock 34,878.38

Audit_Employee Table Results

  • The update_salary trigger stored one row in the audit_employee table.
  • System user named dbo made the update at 1:48 a.m.
  • The audit trail is invisible to any employee making salary modifications.
  • SELECT emp_ssn "SSN",
  • CONVERT(CHAR(10), old_salary, 1) "Old Salary",
  • CONVERT(CHAR(10), new_salary, 1) "New Salary",
  • CAST(system_user_name AS CHAR(8)) "Who",
  • CAST(datetime_changed AS CHAR(23)) "On DateTime"
  • FROM audit_employee;
  • SSN Old Salary New Salary Who On DateTime
  • --------- ---------- ---------- -------- -------------------
  • 999111111 33,217.50 34,878.38 dbo May 9 2003 1:48AM

The DELETED & INSERTED Virtual Tables

  • These two virtual tables are used in the update_salary trigger.
  • The structure for these two virtual tables is automatically created by SQL Server. Their column names/data types are identical to the table (employee in this case) referenced by a trigger.
  • The deleted table is used to refer to values before the action that fires the trigger while the inserted table is used to refer to values after the action that fires the trigger.

The DELETED & INSERTED Virtual Tables (Cont.)

  • When data rows are inserted or deleted, these two virtual tables store copies of the rows that are deleted and inserted.
  • When rows are updated, the deleted virtual table stores copies of the rows before an UPDATE statement executes and the inserted virtual table stores copies of the rows after an UPDATE statement executes.

Trigger Example #2 – Enforcing a Business Rule

  • Example business rule: No employee may receive a pay raise that exceeds 10% of their current base salary.
  • The check_salary_raise trigger checks the % of a raise and if the new employee salary figure is 10% larger than the old salary figure, the UPDATE transaction is canceled through use of a ROLLBACK TRANSACTION statement.
  • The old and new salary figures are stored to variables from the deleted and inserted virtual tables, and compared to evaluate the % of salary raise.

Check_Salary_Raise Trigger Code

  • CREATE TRIGGER check_salary_raise
  • ON employee AFTER UPDATE
  • AS IF UPDATE(emp_salary)
  • BEGIN
  • DECLARE @old_salary MONEY
  • DECLARE @new_salary MONEY
  • SELECT @old_salary = (SELECT emp_salary FROM deleted)
  • SELECT @new_salary = (SELECT emp_salary FROM inserted)
  • IF @new_salary > @old_salary * 1.1
  • BEGIN
  • PRINT 'Salary Raise Exceeds Policy Limits'
  • ROLLBACK TRANSACTION
  • END
  • ELSE
  • BEGIN
  • PRINT 'Salary Raise Approved'
  • END
  • END;

A Valid Raise % Test

  • The EXEC of the individual_raise procedure fires the check_salary_raise trigger as evidenced by the Salary Raise Approved message.
  • EXEC individual_raise @emp_ssn=999111111,
  • @percent_raise=5.0
  • SSN Last Name Old Salary
  • --------- ------------------------- ----------
  • 999111111 Bock 34,878.38
  • Salary Raise Approved
  • SSN Last Name New Salary
  • --------- ------------------------- ----------
  • 999111111 Bock 36,622.29

Audit_Employee Table Results

  • The audit_employee table now has two rows – a new one reflecting the result of the earlier update_salary trigger firing.
  • SELECT emp_ssn "SSN",
  • CONVERT(CHAR(10), old_salary, 1) "Old Salary",
  • CONVERT(CHAR(10), new_salary, 1) "New Salary",
  • CAST(system_user_name AS CHAR(8)) "Who",
  • CAST(datetime_changed AS CHAR(23)) "On DateTime"
  • FROM audit_employee;
  • SSN Old Salary New Salary Who On DateTime
  • --------- --------- ---------- ---- -------------------
  • 999111111 33,217.50 34,878.38 dbo May 9 2003 1:48AM
  • 999111111 34,878.38 36,622.29 dbo May 9 2003 4:36PM

An Invalid Raise % Test

  • An invalid raise of 11% for employee Bordoloi will cause the check_salary_raise trigger to display the Salary Raise Exceeds Policy Limits message.
  • Because the transaction was canceled by the ROLLBACK TRANSACTION statement, the audit_employee table will not have any record of the modification.

An Invalid Raise % Test (Cont.)

  • EXEC individual_raise @emp_ssn=999666666, @percent_raise=11.0
  • SSN Last Name Old Salary
  • --------- --------------------- ----------
  • 999666666 Bordoloi 60,898.75
  • (1 row(s) affected)
  • (1 row(s) affected)
  • Salary Raise Exceeds Policy Limits

Defining the Order of Trigger Execution

  • Because SQL Server allows more than one trigger for a table or view, it is sometimes important to specify the order in which triggers execute.
  • By definition, triggers execute in the order in which they are created.
  • Revise our scenario by dropping the two triggers and recreating the update_salary trigger to include a COMMIT statement; then, recreating the check_salary_raise trigger.

Redefined Update_Salary Trigger

  • CREATE TRIGGER update_salary
  • ON employee AFTER UPDATE
  • AS IF UPDATE(emp_salary)
  • BEGIN
  • DECLARE @emp_ssn CHAR(9)
  • DECLARE @old_salary MONEY
  • DECLARE @new_salary MONEY
  • SELECT @old_salary = (SELECT emp_salary
  • FROM deleted)
  • SELECT @new_salary = (SELECT emp_salary
  • FROM inserted)
  • SELECT @emp_ssn = (SELECT emp_ssn FROM inserted)
  • INSERT INTO audit_employee VALUES
  • (@emp_ssn, @old_salary, @new_salary,
  • USER_NAME(), GETDATE())
  • COMMIT
  • END

Resetting the Trigger Firing Order

  • Use the system stored procedure named sp_settriggerorder (read set trigger order) for an individual event such as an AFTER UPDATE transaction.
  • Use the parameter @order to specify values of either: (1) FIRST, (2) LAST, or (3) NONE – these specify if a trigger is to fire FIRST or LAST as an AFTER trigger.
  • The @stmttype parameter specifies the type of DML transaction – (1) update, (2), delete, or (3) insert.
  • EXEC sp_settriggerorder @triggername='check_salary_raise',
  • @order='first', @stmttype='update'

Testing the Trigger Firing Order

  • Test the trigger firing order by attempting to increase Bordoloi’s salary by 11% (violates the business rule).
  • The check_salary_raise trigger fires first and rejects the pay raise. The update operation is rolled back.
  • EXEC individual_raise @emp_ssn=999666666, @percent_raise=11.0
  • SSN Last Name Old Salary
  • --------- -------------------- ----------
  • 999666666 Bordoloi 67,597.61
  • (1 row(s) affected)
  • Salary Raise Exceeds Policy Limits

Effect of Trigger Firing Order on the Audit_Employee Table

  • Since Bordoloi’s raise was rolled back, will a row still be inserted into the audit_employee table?
  • No, because row insertions would first be written to the inserted virtual table; however, the check_salary_raise trigger fired and rolled the transaction back.
  • Rolling back the transaction canceled the raise and the update_salary trigger never fires.

INSTEAD OF Triggers

  • An INSTEAD OF trigger fires in place of a triggering event such as an UPDATE or INSERT transaction.
  • INSTEAD OF triggers execute after SQL Server creates the inserted and deleted virtual tables, so the data rows for the triggering event are stored to these two virtual tables, but any existing integrity constraints and triggers checking business rules have not yet fired.
  • INSTEAD OF triggers can be created on both views and tables, whereas AFTER triggers can only be created for tables—an important advantage of this type of trigger.
  • INSTEAD OF triggers use the data rows found in the inserted and deleted virtual tables for views that are in use to complete any required DML transaction.

A Project and Equipment View

  • The project_equipment view displays information about equipment used on various projects.
  • The column named eqp_total_value is a computed (derived) column in the equipment base table.
  • CREATE VIEW project_equipment AS
  • SELECT pro_number, pro_name, eqp_no,
  • eqp_description, eqp_value,
  • eqp_qty_on_hand, eqp_total_value
  • FROM project JOIN equipment ON
  • (eqp_pro_number = pro_number)

An Attempted Update

  • Project 30 has one printer allocated (eqp_number = '5678')
  • Allocating another printer of the same type to project 30 requires an UPDATE operation that attempts to use the project_equipment view.
  • This yields an error – the computed column eqp_total_value (referenced as derived in the error message) cannot be specified for update through use of a view. This is a limitation of views.
  • UPDATE project_equipment SET eqp_qty_on_hand = 2,
  • eqp_value = 172.00, eqp_total_value = 344.00
  • WHERE pro_number = 30 AND eqp_no='5678';
  • Server: Msg 4406, Level 16, State 2, Line 1
  • Update or insert of view or function 'project_equipment' failed because it contains a derived or constant field.

Creating an INSTEAD OF Trigger

  • The Trigger fires for the project_equipment view, but updates the equipment table directly from values in the inserted virtual table.
  • CREATE TRIGGER update_eqp_total_value ON project_equipment
  • INSTEAD OF UPDATE
  • AS BEGIN
  • DECLARE @pro_number SMALLINT
  • DECLARE @eqp_no CHAR(4)
  • SELECT @pro_number = (SELECT pro_number
  • FROM inserted)
  • SELECT @eqp_no = (SELECT eqp_no FROM inserted)
  • UPDATE equipment SET eqp_qty_on_hand = 2
  • WHERE eqp_pro_number = @pro_number AND
  • eqp_no=@eqp_no
  • END;

Executing an UPDATE Transaction

  • When the UPDATE transaction shown earlier was executed, the equipment base table was not updated – the UPDATE failed.
  • Now a re-execution of the UPDATE actually causes the UPDATE statement specified as part of the INSTEAD OF trigger executes.
  • Now the update executes and the new information for the equipment and project is shown by the SELECT statement.
  • The value stored in the eqp_total_value column was automatically updated to $344.00 because this column is derived.
  • SELECT pro_number, eqp_no, eqp_value, eqp_qty_on_hand,
  • eqp_total_value
  • FROM project_equipment
  • WHERE pro_number = 30 and eqp_no='5678';
  • pro_number eqp_no eqp_value eqp_qty_on_hand eqp_total_value
  • ---------- ------ --------- --------------- ---------------
  • 30 5678 172.0000 2 344.0000

Summary

  • Stored procedures are small programs that exist as database objects.
    • They can automate batch process for tasks such as audit trail creation.
    • Stored procedures execute efficiently as they are compiled and stored as database objects.
  • Triggers are database objects that fire based on specified DML events.
    • Triggers enforce business rules.
    • AFTER triggers can enforce integrity constraints for tables while INSTEAD OF triggers can enforce integrity constraints for both views and tables.
    • The order of trigger execution when a table has multiple triggers is specified with the sp_settriggerorder system procedure.


The database is protected by copyright ©sckool.org 2016
send message

    Main page