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
// |
Partager