SQL for Database Construction and Application Processing - Database Processing: Fundamentals, Design, and Implementation - page 330

that no change will be made to the database because the DBMS left all processing tasks to the
INSTEAD OF trigger.
The trigger on DeleteEmployeeDepartment, shown in Figure 7-29, treats the employee
deletion a bit differently. First, the trigger checks to determine if the employee is the last em-
ployee in the department. If so, the EMPLOYEE is deleted, and then the DEPARTMENT itself is
deleted. Notice that the row in EMPLOYEE is deleted in either case.
Triggers such as those in Figures 7-28 and 7-29 are used to enforce the referential integrity
actions for O-M and M-M relationships, as described at the end of Chapter 6. You will learn
how to write them for Microsoft SQL Server 2012 in Chapter 10A, Oracle Database 11 g Release
2 in Chapter 10B, and for MySQL 5.6 in Chapter 10C.

Using Stored Procedures
A stored procedure is a program that is stored within the database and compiled when
used. In Oracle Database, stored procedures can be written in PL/SQL or in Java. With SQL
Server 2012, stored procedures are written in T-SQL or a .NET CLR language, such as Visual
Basic.NET, C#.NET, or C++.NET. With MySQL, stored procedures are written in MySQL's
variant of SQL.
Stored procedures can receive input parameters and return results. Unlike triggers, which
are attached to a given table or view, stored procedures are attached to the database. They
can be executed by any process using the database that has permission to use the procedure.
Differences between triggers and stored procedures are summarized in Figure 7-30.
Stored procedures are used for many purposes. Although database administrators use
them to perform common administration tasks, their primary use is within database applica-
tions. They can be invoked from application programs written in languages such as COBOL, C,
Java, C#, or C++. They also can be invoked from Web pages (as we will see in Chapter 11) using
VBScript, JavaScript, or PHP. Ad hoc users can run them from DBMS management products
such as SQL*Plus or SQL Developer in Oracle Database, SQL Server Management Studio in
SQL Server, or the MySQLWorkbench in MySQL.

&5($7(75,**(5(03/2<((B'(3$570(17B'HOHWH&KHFN

,167($'2)'(/(7(21'HOHWH(PSOR\HH'HSDUWPHQW9LHZ

'(&/$5(

URZ&RXQW ,QW

%(*,1

)LUVWGHWHUPLQHLIWKLVLVWKHODVWHPSOR\HHLQWKHGHSDUWPHQW

6(/(&7 &RXQWLQWRURZ&RXQW

)520 (03/2<((

:+(5( (03/2<(((PSOR\HH1XPEHU ROG(PSOR\HH1XPEHU

'HOHWH(PSOR\HHURZUHJDUGOHVVRIZKHWKHU'HSDUWPHQWLVGHOHWHG

'(/(7( (03/2<((

:+(5( (03/2<(((PSOR\HH1XPEHU ROG(PSOR\HH1XPEHU

,)URZ&RXQW

7+(1

/DVWHPSOR\HHLQ'HSDUWPHQWGHOHWH'HSDUWPHQW

Figure 7-29
trigger Code to
Delete Last
Child and
Parent When
Necessary

'(/(7( '(3$570(17

:+(5( '(3$570(17'HSDUWPHQW1DPH ROG'HSDUWPHQW1DPH

(1',)

(1'