Database Reference
In-Depth Information
least the AskingPrice. To enforce this rule, the gallery database has an insert and update trig-
ger on TRANS that checks to see if the work is by a Mexican painter. If so, the SalesPrice is
checked against the AskingPrice. If it is less than the AskingPrice, the SalesPrice is reset to the
AskingPrice. This, of course, must happen when the artwork is actually being sold, and the
customer charged the full amount! This is not a postsale accounting adjustment.
Figure 7-26 shows generic trigger code that implements this rule. This trigger will be fired
after any insert or update on a TRANS row. The trigger first checks to determine if the work
is by a Mexican artist. If not, the trigger is exited. Otherwise, the SalesPrice is checked against
the AskingPrice; if it is less than the AskingPrice, the SalesPrice is set equal to the AskingPrice.
This trigger will be called recursively; the update statement in the trigger will cause an update
on TRANS, which will cause the trigger to be called again. The second time, however, the SalesPrice
will be equal to the AskingPrice, no more updates will be made, and the recursion will stop.
Using Triggers to Update Views
As stated earlier, the DBMS can update some views but not others, depending on the way the
view is constructed. Applications can sometimes update the views that the DBMS cannot
update by applying logic that is particular to a given business setting. In this case, the applica-
tion-specific logic for updating the view is placed in an INSTEAD OF trigger.
Figure 7-26
trigger Code to Enforce an
Interrelation Data Constraint
&5($7(75,**(575$16B&KHFN6DOHV3ULFH
$)7(5,16(5783'$7(2175$16
'(&/$5(
DUWLVW1DWLRQDOLW\ &KDU
%(*,1
)LUVWGHWHUPLQHLIZRUNLVE\D0H[LFDQDUWLVW
6(/(&7 1DWLRQDOLW\LQWRDUWLVW1DWLRQDOLW\
)520 $57,67$6$-2,1:25.$6:
21$$UWLVW,' :$UWLVW,'
:+(5( ::RUN,' QHZ:RUN,'
,)DUWLVW1DWLRQDOLW\!0H[LFDQ
7+(1
([LW7ULJJHU
(/6(
:RUNLVE\D0H[LFDQDUWLVWHQIRUFHFRQVWUDLQW
,)QHZ6DOHV3ULFHQHZ$VNLQJ3ULFH
7+(1
6DOHV3ULFHLVWRRORZUHVHWLW
83'$7( 75$16
6(76DOHV3ULFH QHZ$VNLQJ3ULFH
1RWH7KHDERYHXSGDWHZLOOFDXVHDUHFXUVLYHFDOORQWKLV
WULJJHU7KHUHFXUVLRQZLOOVWRSWKHVHFRQGWLPHWKURXJK
EHFDXVH6DOHV3ULFHZLOOEH $VNLQJ3ULFH
$WWKLVSRLQWVHQGDPHVVDJHWRWKHXVHUVD\LQJZKDW¶VEHHQ
GRQHVRWKDWWKHFXVWRPHUKDVWRSD\WKHIXOODPRXQW
(/6(
(UURUQHZ6DOHV3ULFH! QHZ$VNLQJ3ULFH
'RVRPHWKLQJ
(1',)
(1',)
(1'
 
Search WWH ::




Custom Search