T4b4U3xL

1DROP TABLE IF EXISTS `test`;
2CREATE TABLE `test` (
3 `id` int(11) NOT NULL AUTO_INCREMENT,
4 `a` int(11) NOT NULL,
5 `b` int(11) NOT NULL,
6 PRIMARY KEY (`id`));
7
8
9INSERT INTO `test` (`id`, `a`, `b`)
10VALUES (1, 1, 2);
11
12INSERT INTO `test` (`id`, `a`, `b`)
13VALUES (2, 3, 4);
14	
15DELIMITER //
16DROP TRIGGER IF EXISTS prevent_multiple_deletion;
17CREATE TRIGGER prevent_multiple_deletion
18BEFORE DELETE ON test
19FOR EACH STATEMENT 
20BEGIN
21
22  IF(ROW_COUNT()>=2) THEN  
23    SIGNAL SQLSTATE '45000' 
24    SET MESSAGE_TEXT = 'Cannot delete more than one order per time!';
25  END IF;
26
27END //
28
29DELIMITER ;
30	
31DELETE FROM `test` WHERE `id`< 5;
32	
33DELIMITER //
34DROP TRIGGER IF EXISTS prevent_multiple_deletion //
35CREATE TRIGGER prevent_multiple_deletion
36BEFORE DELETE ON `test`
37FOR EACH ROW  
38BEGIN
39
40  IF( @rows_being_deleted IS NULL ) THEN 
41    SET @rows_being_deleted = 1;
42  ELSE 
43    SET @rows_being_deleted = @rows_being_deleted + 1;
44    SIGNAL SQLSTATE '45000' 
45    SET MESSAGE_TEXT = 'Cannot delete more than one order per time!';
46  END IF;
47
48END //
49
50DELIMITER ;