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
|
-- Trigger DDL Statements
USE `MySchema`;
DELIMITER //
CREATE TRIGGER ins_key_client BEFORE INSERT ON t_client FOR EACH ROW
BEGIN
DECLARE ok INT UNSIGNED;
DECLARE table_name VARCHAR(50);
DECLARE last_id VARCHAR(10);
SET ok = 0;
SET table_name = 't_client';
-- on regarde si la table concernée existe
SELECT count(*) INTO ok
FROM t_identifiant
WHERE id_nom_table = table_name;
IF ok = 0 THEN
-- on crée l'enregistrement
SET last_id = CONCAT(CAST(YEAR(CURDATE()) AS CHAR),'000000');
INSERT INTO t_identifiant VALUES (table_name,last_id);
END IF;
-- calcul de la nouvelle clé
SET last_id = '';
SELECT CAST(CAST(last_value AS UNSIGNED)+1 AS CHAR) INTO last_id
FROM t_identifiant
WHERE id_nom_table = table_name;
-- on vérifie que c'est la bonne année
IF LEFT(last_id,4) < CAST(YEAR(CURDATE()) AS CHAR) THEN
SET last_id = CONCAT(CAST(YEAR(CURDATE()) AS CHAR),'000001');
END IF;
-- maj de la clé
UPDATE t_identifiant
SET last_value = last_id
WHERE id_nom_table = table_name;
SET NEW.id_client = last_id;
END
// |
