1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124
| --------------
START TRANSACTION
--------------
--------------
DROP DATABASE IF EXISTS `base`
--------------
--------------
CREATE DATABASE IF NOT EXISTS `base`
DEFAULT CHARACTER SET `latin1`
DEFAULT COLLATE `latin1_general_ci`
--------------
--------------
DROP TABLE IF EXISTS `client`
--------------
--------------
CREATE TABLE `client`
( `id` integer unsigned not null auto_increment primary key,
`nom` varchar(255) not null,
`prenom` varchar(255) not null
) ENGINE=InnoDB
DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
INSERT INTO `client` (`nom`,`prenom`) values
('Nom 1','Prénom 1'),('Nom 2','Prénom 1'),('Nom 3','Prénom 3')
--------------
--------------
select * from `client`
--------------
+----+-------+----------+
| id | nom | prenom |
+----+-------+----------+
| 1 | Nom 1 | Prénom 1 |
| 2 | Nom 2 | Prénom 1 |
| 3 | Nom 3 | Prénom 3 |
+----+-------+----------+
--------------
DROP TABLE IF EXISTS `telephone`
--------------
--------------
CREATE TABLE `telephone`
( `id` integer unsigned not null auto_increment primary key,
`client_fk` integer unsigned not null,
`numero` char(10) not null,
CONSTRAINT `FK_01` FOREIGN KEY (`client_fk`) REFERENCES `client` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
DROP TRIGGER IF EXISTS `verify`
--------------
--------------
CREATE TRIGGER `verify`
BEFORE INSERT ON `telephone`
FOR EACH ROW
BEGIN
DECLARE _cli integer DEFAULT NULL;
DECLARE _msg varchar(255);
IF ((select 1 from `client` where id = new.client_fk) IS NULL) THEN
set _msg = concat('Le client ', new.client_fk, ' n''existe pas !');
SIGNAL SQLSTATE VALUE '07777' SET MESSAGE_TEXT = _msg, MYSQL_ERRNO = 1;
END IF;
select client_fk into _cli from `telephone` where numero = new.numero;
IF (_cli IS NOT NULL) THEN
set _msg = concat('Le numero de téléphone ', new.numero, ' du client ', new.client_fk, ' est déjà utilisé par le client ', _cli, ' !');
SIGNAL SQLSTATE VALUE '07777' SET MESSAGE_TEXT = _msg, MYSQL_ERRNO = 2;
END IF;
END
--------------
--------------
INSERT INTO `telephone` (`client_fk`,`numero`) values (1,'1234567890')
--------------
--------------
INSERT INTO `telephone` (`client_fk`,`numero`) values (2,'1357924680')
--------------
--------------
INSERT INTO `telephone` (`client_fk`,`numero`) values (2,'1234567890')
--------------
ERROR 2 (07777) at line 94: Le numero de téléphone 1234567890 du client 2 est déjà utilisé par le client 1 !
--------------
INSERT INTO `telephone` (`client_fk`,`numero`) values (4,'4567890123')
--------------
ERROR 1 (07777) at line 95: Le client 4 n'existe pas !
--------------
INSERT INTO `telephone` (`client_fk`,`numero`) values (3,'4567890123')
--------------
--------------
select * from `telephone`
--------------
+----+-----------+------------+
| id | client_fk | numero |
+----+-----------+------------+
| 1 | 1 | 1234567890 |
| 2 | 2 | 1357924680 |
| 3 | 3 | 4567890123 |
+----+-----------+------------+
--------------
COMMIT
--------------
Appuyez sur une touche pour continuer... |
Partager