Database Reference
In-Depth Information
&5($7(75,**(575$16B$VNLQJ3ULFH,QLWLDO9DOXH
$)7(5,16(572175$16
'(&/$5(
URZ&RXQW ,QW
VXP1HW3URILW 1XPHULF
DYJ1HW3URILW 1XPHULF
%(*,1
)LUVWILQGLIZRUNKDVEHHQKHUHEHIRUH
6(/(&7 &RXQW,172URZ&RXQW
)520 75$16$67
:+(5( QHZ:RUN,' 7:RUN,'
,)URZFRXQW
7+(1
7KLVLVILUVWWLPHZRUNKDVEHHQLQJDOOHU\
QHZ$VNLQJ3ULFH QHZ$FTXLVLWLRQ3ULFH
(/6(
,)URZFRXQW!
7+(1
:RUNKDVEHHQKHUHEHIRUH
6(/(&7 6801HW3URILWLQWRVXP1HW3URILW
)520 $UWLVW:RUN1HW9LHZ$:19
:+(5( $:19:RUN,' QHZ:RUN,'
*5283%< $:19:RUN,'
DYJ1HW3URILW VXP1HW3URILWURZ&RXQW±
1RZFKRRVHODUJHUYDOXHIRUWKHQHZ$VNLQJ3ULFH
,)QHZ$FTXLVLWLRQ3ULFHDYJ1HW3URILW
!QHZ$FTXLVLWLRQ3ULFH
7+(1
QHZ$VNLQJ3ULFH QHZ$FTXLVLWLRQ3ULFHDYJ1HW3URILW
(/6(
QHZ$VNLQJ3ULFH QHZ$FTXLVLWLRQ3ULFH
(1',)
(/6(
(UURUURZ&RXQWFDQQRWEHOHVVWKDQ
'RVRPHWKLQJ
(1',)
(1',)
(1'
described on page 315 to compute SUM(NetProfit) for this work. The sum is placed in the vari-
able sumNetProfit. Notice that the WHERE clause limits the rows to be used in the view to this
particular work. The average is then computed by dividing this sum by rowCount minus one.
You may be wondering, why not use AVG(NetProfit) in the SQL statement? The answer is
that the default SQL average function would have counted the new row in the computation of
the average. We do not want that row to be included, so we subtract one from rowCount when
the average is computed. Once the value of avgNetProfit has been computed, it is compared
with twice the AcquisitionPrice; the larger result is used for the new value of AskingPrice.
Figure 7-25
trigger Code to Insert a
Default Value
Using Triggers to Enforce Data Constraints
A second purpose of triggers is to enforce data constraints. Although SQL CHECK constraints
can be used to enforce domain, range, and intrarelation constraints, no DBMS vendor has
implemented the SQL-92 features for interrelation CHECK constraints. Consequently, such
constraints are implemented in triggers.
Suppose, for example, that the gallery has a special interest in Mexican painters and
never discounts the price of their works. Thus, the SalesPrice of a work must always be at
 
Search WWH ::




Custom Search