Database Reference
In-Depth Information
CREATE
TRIGGER
bi_cust_invoice
BEFORE
INSERT
ON
cust_invoice
FOR
EACH
ROW
SET
NEW
.
tax_rate
=
sales_tax_rate
(
NEW
.
state
);
Within the trigger,
NEW.
col_name
refers to the new value to be inserted into the given
column. By assigning a value to
NEW.
col_name
within the trigger, you cause the column
to have that value in the new row.
This trigger is simple and its body contains only a single SQL statement. For a trigger
body that executes multiple statements, use
BEGIN
…
END
compound-statement syntax.
In that case, if you use
mysql
to create the trigger, change the statement delimiter while
you define the trigger, as discussed in
Recipe 9.1
.
To test the implementation, insert a row and check whether the trigger correctly initi‐
alizes the sales tax rate for the invoice:
mysql>
INSERT INTO cust_invoice (state,amount) VALUES('NY',100);
mysql>
SELECT * FROM cust_invoice WHERE id = LAST_INSERT_ID();
+----+-------+--------+----------+
| id | state | amount | tax_rate |
+----+-------+--------+----------+
| 1 | NY | 100.00 | 0.09 |
+----+-------+--------+----------+
The
SELECT
shows that the
tax_rate
column has the right value even though the
IN
SERT
provides no value for it.
9.5. Using Triggers to Simulate Function-Based Indexes
Problem
You need a function-based index, but MySQL doesn't support that capability.
Solution
Use a secondary column and triggers to simulate a function-based index.
Discussion
Some types of information are more easily analyzed using not the original values, but
an expression computed from them. For example, if data values lie along an exponential
curve, applying a logarithmic transform to them yields a more linear scale. Queries
against a table that stores exponential values might therefore typically use expressions
that refer to the log values:
SELECT
*
FROM
expdata
WHERE
LOG10
(
value
)
<
2
;
A disadvantage of such expressions is that referring to the
value
column within a func‐
tion call prevents the optimizer from using any index on it. MySQL must retrieve the
