Pkg6Sq9z

1CREATE TABLE _sequence
2(
3    seq_name VARCHAR(10) NOT NULL PRIMARY KEY,
4    seq_group VARCHAR(10) NOT NULL,
5    seq_val INT UNSIGNED NOT NULL
6);
7
8delimiter //
9DROP FUNCTION IF EXISTS getNextCustomSeq//
10CREATE FUNCTION getNextCustomSeq
11(
12    sSeqName VARCHAR(10),
13    sSeqGroup VARCHAR(10)
14) RETURNS VARCHAR(10)
15BEGIN
16    DECLARE nLast_val INT; 
17 
18    SET nLast_val =  (SELECT seq_val
19                          FROM _sequence
20                          WHERE seq_name = sSeqName
21                                AND seq_group = sSeqGroup);
22    IF nLast_val IS NULL THEN
23        SET nLast_val = 1;
24        INSERT INTO _sequence (seq_name,seq_group,seq_val)
25        VALUES (sSeqName,sSeqGroup,nLast_Val);
26    ELSE
27        SET nLast_val = nLast_val + 1;
28        UPDATE _sequence SET seq_val = nLast_val
29        WHERE seq_name = sSeqName AND seq_group = sSeqGroup;
30    END IF; 
31 
32    SET @ret = (SELECT concat(sSeqGroup,'-',lpad(nLast_val,4,'0')));
33    RETURN @ret;
34END// 
35 
36DROP PROCEDURE IF EXISTS sp_setSeqCustomVal//
37CREATE PROCEDURE sp_setCustomVal(sSeqName VARCHAR(10),  
38              sSeqGroup VARCHAR(10), nVal INT UNSIGNED)
39BEGIN
40    IF (SELECT COUNT(*) FROM _sequence  
41            WHERE seq_name = sSeqName  
42                AND seq_group = sSeqGroup) = 0 THEN
43        INSERT INTO _sequence (seq_name,seq_group,seq_val)
44        VALUES (sSeqName,sSeqGroup,nVal);
45    ELSE
46        UPDATE _sequence SET seq_val = nVal
47        WHERE seq_name = sSeqName AND seq_group = sSeqGroup;
48    END IF;
49END//
50
51delimiter //
52DROP TRIGGER IF EXISTS year_kayitNo//
53 
54CREATE TRIGGER year_kayitNo BEFORE INSERT ON Kayit
55FOR each ROW
56BEGIN
57   SET NEW.kayitNo = getNextCustomSeq(year(now()),year(now()));
58END//