Database Reference
In-Depth Information
When an INSTEAD OF trigger is declared on a view, the DBMS performs no action other
than to call the trigger. Everything else is up to the trigger. If you declare an INSTEAD OF
INSERT trigger on view MyView, and if your trigger does nothing but send an e-mail mes-
sage, then that e-mail message becomes the result of an INSERT on the view. INSERT MyView
means “send an e-mail” and nothing more.
More realistically, consider the SQL view CustomerInterestsView on page 314 and the
result of that view in Figure 7-18. This view is the result of two joins across the intersection
table between CUSTOMER and ARTIST. Suppose that this view populates a grid on a user
form, and further suppose that users want to make customer name corrections, when neces-
sary, on this form. If such changes are not possible, the users will say something like, “But, hey,
the name is right there. Why can't I change it?” Little do they know the trials and tribulations
the DBMS went through to display those data!
In any case, if, for example, the customer LastName value happens to be unique within the
database, the view has sufficient information to update the customer's last name. Figure 7-27
shows generic trigger code for such an update. The code just counts the number of customers that
have the old value of LastName. If only one customer has that value, then the update is made; oth-
erwise, an error message is generated. Notice that the update activity is on one of the tables that
underlie the view. The view, of course, has no real view data. Only actual tables can be updated.
Using Triggers to Implement Referential Integrity Actions
The fourth use of triggers is to implement referential integrity actions. Consider, for example,
the 1:N relationship between DEPARTMENT and EMPLOYEE. Assume that the relationship is
M-M and that EMPLOYEE.DepartmentName is a foreign key to DEPARTMENT.
To enforce this constraint, we will construct two views, both based on EMPLOYEE. The
first view, DeleteEmployeeView, will delete an EMPLOYEE row only if that row is not the last
Figure 7-27
trigger Code to
Update an SQL
View
&5($7(75,**(5&XVWRPHU,QWHUHVW9LHZB8SGDWH&XVWRPHU/DVW1DPH
,167($'2)83'$7(21&XVWRPHU,QWHUHVW9LHZ
'(&/$5(
URZ&RXQW ,QW
%(*,1
6(/(&7 &2817LQWRURZ&RXQW
)520 &86720(5
:+(5( &86720(5/DVW1DPH ROG/DVW1DPH
,)URZFRXQW
7+(1
,IJHWKHUHWKHQRQO\RQHFXVWRPHUKDVWKLVODVWQDPH
0DNHWKHQDPHFKDQJH
83'$7( &86720(5
6(7&86720(5/DVW1DPH QHZ/DVW1DPH
:+(5( &86720(5/DVW1DPH ROG/DVW1DPH
(/6(
,)URZ&RXQW!
7+(1
6HQGDPHVVDJHWRWKHXVHUVD\LQJFDQQRWXSGDWHEHFDXVH
WKHUHDUHWRRPDQ\FXVWRPHUVZLWKWKLVODVWQDPH
(/6(
(UURULIURZFRXQW WKHUHLVDQHUURU
'RVRPHWKLQJ
(1',)
(1',)
(1'
Search WWH ::
Custom Search