Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE TABLE _sequence
- (
- seq_name VARCHAR(50) NOT NULL PRIMARY KEY,
- seq_group VARCHAR(10) NOT NULL,
- seq_val INT UNSIGNED NOT NULL
- );
- delimiter //
- DROP FUNCTION IF EXISTS getNextCustomSeq//
- CREATE FUNCTION getNextCustomSeq
- (
- sSeqName VARCHAR(50),
- sSeqGroup VARCHAR(10)
- ) RETURNS VARCHAR(20)
- BEGIN
- DECLARE nLast_val INT;
- SET nLast_val = (SELECT seq_val
- FROM _sequence
- WHERE seq_name = sSeqName
- AND seq_group = sSeqGroup);
- IF nLast_val IS NULL THEN
- SET nLast_val = 1;
- INSERT INTO _sequence (seq_name,seq_group,seq_val)
- VALUES (sSeqName,sSeqGroup,nLast_Val);
- ELSE
- SET nLast_val = nLast_val + 1;
- UPDATE _sequence SET seq_val = nLast_val
- WHERE seq_name = sSeqName AND seq_group = sSeqGroup;
- END IF;
- SET @ret = (SELECT concat(sSeqGroup,'-',nLast_val));
- RETURN @ret;
- END//
- delimiter ;
- DROP TRIGGER IF EXISTS tr_custom_autonums//
- CREATE TRIGGER tr_custom_autonums BEFORE INSERT ON tb_pegawai
- FOR each ROW
- BEGIN
- SET NEW.id_pegawai = getNextCustomSeq("id_pegawai","PG");
- END//
- delimiter ;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement