Database Reference
In-Depth Information
Since SQL Server 2005, SQL Server supports DDL triggers (triggers on such SQL DDL
statements as CREATE, ALTER, and DROP) as well as DML triggers. We will only deal with
the DML triggers here, which for SQL Server 2012 are INSTEAD OF and AFTER triggers on
INSERT, UPDATE, and DELETE. (Microsoft includes the FOR keyword, but this is a synonym
for AFTER in Microsoft syntax.) Thus, we have six possible trigger types.
MySQL 5.6 supports only BEFORE and AFTER triggers, thus it supports only six trigger
types. Other DBMS products support triggers differently. See the documentation of your prod-
uct to determine which trigger types it supports.
When a trigger is invoked, the DBMS makes the data involved in the requested action
available to the trigger code. For an insert, the DBMS will supply the values of columns for the
row that is being inserted. For deletions, the DBMS will supply the values of columns for the
row that is being deleted. For updates, it will supply both the old and the new values.
The way in which this is done depends on the DBMS product. For now, assume that
new values are supplied by prefixing a column name with the expression
new:
. Thus, during
an insert on CUSTOMER, the variable new:LastName is the value of LastName for the row
being inserted. For an update, new:LastName has the value of LastName after the update
takes place. Similarly, assume that old values are supplied by prefixing a column name with
the expression
old:
. Thus, for a deletion, the variable old:LastName has the value of LastName
for the row being deleted. For an update, old:LastName has the value of Name prior to the
requested update. This, in fact, is the strategy used by Oracle PL/SQL and MySQL SQL—you
will see the equivalent SQL Server strategy in Chapter 10A.
Triggers have many uses. In this chapter, we consider the four uses summarized in
Figure 7-24:
●
Providing default values
●
Enforcing data constraints
●
Updating SQL views
●
Performing referential integrity actions
Using Triggers to Provide Default Values
Earlier in this chapter, you learned to use the SQL DEFAULT keyword to provide initial column
values. DEFAULT works only for simple expressions, however. If the computation of a default
value requires complicated logic, then an INSERT trigger must be used instead.
For example, suppose that there is a policy at View Ridge Gallery to set the value of
AskingPrice equal either to twice the AcquisitionPrice or to the AcquisitionPrice plus the
average net gain for sales of this art in the past, whichever is greater. The AFTER trigger in
Figure 7-25 implements this policy. Note that the code in Figure 7-25, although resembling
Oracle Database PL/SQL, is generic pseudocode. You will learn how to write specific code for
SQL Server, Oracle Database, and MySQL in Chapters 10A, 10B, and 10C, respectively.
After declaring program variables, the trigger reads the TRANS table to find out how many
TRANS rows exist for this work. Because this is an AFTER trigger, the new TRANS row for the
work will have already been inserted. Thus, the count will be one if this is the first time the work
has been in the gallery. If so, the new value of SalesPrice is set to twice the AcquisitionPrice.
If the user variable
rowCount
is greater than one, then the work has been in the gallery
before. To compute the average gain for this work, the trigger uses the ArtistWorkNetView
Figure 7-24
Uses for SQL triggers
Uses of SQL Triggers
Provide default values.
Enforce data constraints.
Update views.
Perform referential integrity actions.
Search WWH ::
Custom Search