11.3 in which a bug was fixed just in
time for this exercise), we obtain:
DELIMITER $$
--
-- Procedures
Chapter 18
[ 275 ]
--
CREATE DEFINER=`marc`@`%` PROCEDURE `add_page`(IN param_isbn
VARCHAR(25), IN param_pages INT, OUT param_message VARCHAR(100))
BEGIN
IF param_pages > 100 THEN
SET param_message = 'the number of pages is too big';
ELSE
UPDATE book SET page_count = page_count + param_pages WHERE
isbn=param_isbn;
SET param_message = 'success';
END IF;
END$$
--
-- Functions
--
CREATE DEFINER=`marc`@`%` FUNCTION `get_country_name`(param_country_
code CHAR(2)) RETURNS varchar(50) CHARSET latin1
BEGIN
DECLARE var_country_name VARCHAR(50) DEFAULT 'not found';
SELECT description into var_country_name FROM country WHERE code =
param_country_code;
RETURN var_country_name;
END$$
DELIMITER ;
Triggers
Triggers are code that we associate to a table to be executed when certain events
occur, for example after a new INSERT in table book. The event does not have to
happen from within phpMyAdmin.
Contrary to routines that are related to a whole database and visible on the
database's Structure page, triggers for each table are accessed from this specific table's
Structure page.
Prior to MySQL 5.1.6, we needed the SUPER privilege to create and delete
triggers. In version 5.1.6, a TRIGGER table-level privilege was added
to the privilege system, so a user no longer needs the powerful SUPER
privilege for these tasks.
Pages:
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253