View difference between Paste ID: wZKEt2ad and NncfHDkA
SHOW: | | - or go back to the newest paste.
1
CREATE TABLE _sequence
2
(
3
    seq_name VARCHAR(50) NOT NULL PRIMARY KEY,
4
    seq_group VARCHAR(10) NOT NULL,
5
    seq_val INT UNSIGNED NOT NULL
6
);
7
8
delimiter //
9
DROP FUNCTION IF EXISTS getNextCustomSeq//
10
CREATE FUNCTION getNextCustomSeq
11
(
12
    sSeqName VARCHAR(50),
13
    sSeqGroup VARCHAR(10)
14
) RETURNS VARCHAR(20)
15
BEGIN
16
    DECLARE nLast_val INT; 
17
    SET nLast_val =  (SELECT seq_val
18
                          FROM _sequence
19
                          WHERE seq_name = sSeqName
20
                                AND seq_group = sSeqGroup);
21
    IF nLast_val IS NULL THEN
22
        SET nLast_val = 1;
23
        INSERT INTO _sequence (seq_name,seq_group,seq_val)
24
        VALUES (sSeqName,sSeqGroup,nLast_Val);
25
    ELSE
26
        SET nLast_val = nLast_val + 1;
27
        UPDATE _sequence SET seq_val = nLast_val
28
        WHERE seq_name = sSeqName AND seq_group = sSeqGroup;
29
    END IF; 
30
    SET @ret = (SELECT concat(sSeqGroup,'-',nLast_val));
31
    RETURN @ret;
32
END// 
33
delimiter ;
34
35
36
DROP TRIGGER IF EXISTS tr_custom_autonums//
37
CREATE TRIGGER tr_custom_autonums BEFORE INSERT ON tb_pegawai
38
FOR each ROW
39
BEGIN
40-
   SET NEW.id_pegawai = getNextCustomSeq("PG","1");
40+
   SET NEW.id_pegawai = getNextCustomSeq("id_pegawai","PG");
41
END//
42
delimiter ;