Database Reference
In-Depth Information
child in the DEPARTMENT. The second view, DeleteEmployeeDepartmentView, will delete an
EMPLOYEE row, and if that row is the last EMPLOYEE in the DEPARTMENT, it will also delete
the DEPARTMENT row.
An organization would make the view DeleteEmployeeView available to ap-
plications that do not have permission to delete a row in DEPARTMENT. The view
DeleteEmployeeDepartmentView would be given to applications that have permission to
delete both employees and departments that have no employees.
Both of the views DeleteEmployeeView and DeleteEmployeeDepartmentView have the
identical structure:
/* *** EXAMPLE CODE - DO NOT RUN *** */
/* *** SQL-CREATE-VIEW-CH07-09 *** */
CREATE VIEW DeleteEmployeeView AS
SELECT *
FROM
EMPLOYEE;
/* *** EXAMPLE CODE - DO NOT RUN *** */
/* *** SQL-CREATE-VIEW-CH07-10 *** */
CREATE VIEW DeleteEmployeeDepartmentView AS
SELECT *
FROM
EMPLOYEE;
The trigger on DeleteEmployeeView, shown in Figure 7-28, determines if the employee
is the last employee in the department. If not, the EMPLOYEE row is deleted. If, however, the
employee is the last employee in the department, nothing is done. Note again that the DBMS
does nothing when an INSTEAD OF trigger is declared on the deletion. All activity is up to
the trigger. If the employee is the last employee, then this trigger does nothing, which means
Figure 7-28
trigger Code to
Delete all but Last
Child
&5($7(75,**(5(03/2<((B'HOHWH&KHFN
,167($'2)'(/(7(21'HOHWH(PSOR\HH9LHZ
'(&/$5(
URZ&RXQW ,QW
%(*,1
)LUVWGHWHUPLQHLIWKLVLVWKHODVWHPSOR\HHLQWKHGHSDUWPHQW
6(/(&7 &RXQWLQWRURZ&RXQW
)520 (03/2<((
:+(5( (03/2<(((PSOR\HH1XPEHU ROG(PSOR\HH1XPEHU
,)URZ&RXQW!
7+(1
1RWODVWHPSOR\HHDOORZGHOHWLRQ
'(/(7( (03/2<((
:+(5( (03/2<(((PSOR\HH1XPEHU ROG(PSOR\HH1XPEHU
(/6(
6HQGDPHVVDJHWRXVHUVD\LQJWKDWWKHODVWHPSOR\HH
LQDGHSDUWPHQWFDQQRWEHGHOHWHG
(1',)
(1'
Search WWH ::




Custom Search