MySQL 5.0 Features
[ 276 ]
In order to perform the following exercise, we'll need a new column in our author
table, total_page_count.
The idea here is that every time a new book is created, its page count will be added
to the author's total page count. I know that some people may advocate that it would
be better to not keep a separate column for the total here and compute the total
instead, each time we need it. In fact, a design decision must be made when dealing
with this situation in the real world: do we need to retrieve the total page count very
quickly, for example for web purposes? And what is the response time to compute
this value from a production table with thousands of rows? Anyway, since I need it
as an example, the design decision is easy to make here.
Let's not forget that following its addition to the table's structure, the
total_page_count column should be initially seeded with the correct total,
which is not the purpose of our trigger.
Manually Creating a Trigger
The current phpMyAdmin version does not have an interface for trigger creation,
therefore, we input in a query box the trigger definition, taking special care of
changing the delimiter box:
Chapter 18
[ 277 ]
Afterwards, the Structure page for our book table reveals a new Triggers section,
which can be used the same way as for routines to edit or delete a trigger:
Testing the Trigger
Contrary to testing stored procedures or functions, there is neither a CALL sequence
nor a function inside a SELECT statement to execute the trigger.
Pages:
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254