I have a table having 3 columns CANDIDATE_ID, SERIAL_NUMBER, and created_by_user_id. My requirement is that if I have to insert serial_number as per created_by_user_id. eg. If I have 2 created_by_user_id say 100 and 101. If I am inserting a candidate_id = 1 and created_by_user_id = 100 then SERIAL_NUMBER should be inserted as 1. If again I insert a candidate_id whose created_by_user_id = 100 then SERIAL_NUMBER should be inserted as 2, but if created_by_user_id = 101 then SERIAL_NUMBER corresponding to that should be inserted as 1. My table name is candidate and I am using MySQL.
I have tried using triggers but it is not working properly as it is inserting NULL every time for SERIAL_NUMBER.
Please find the below code I have used and please help me in writing the correct code. Also, If you can write and provide the complete code that would be of great help.
DELIMITER $$ CREATE TRIGGER trig_SerialNumber BEFORE INSERT ON candidate FOR EACH ROW BEGIN DECLARE srNo INT ; IF Old.SERIAL_NUMBER IS NULL THEN SET New.SERIAL_NUMBER = 1 ; ELSE select (MAX(SERIAL_NUMBER) + 1) INTO srNo from candidate where Created_by_User_Id = NEW.Created_by_User_Id; SET New.SERIAL_NUMBER = srNo; END IF; END $$ DELIMITER ;
this is my table after a few inserts
candidate_id SERIAL_NUMBER created_by_user_id 1 NULL 101 2 NULL 102 3 NULL 101