MySQL Trigger not inserting expected value

Question

Business requirement: When a patient record is inserted, we want to have an ID generated of the format 001-2022, 002-2022 (serial no. plus year). When the year changes, the serial number should start with 1 again. I wrote the following MySQL trigger before Patient record insert:

delimiter //
drop trigger generate_yearly_id;
CREATE TRIGGER generate_yearly_id BEFORE insert ON patients
       FOR EACH ROW
       BEGIN
           DECLARE last_id integer;
           DECLARE current_year date;
           DECLARE yearly_id integer;
           IF new.CustomYearlyId is null then
           begin
                
                set @last_id := (select max(yearlyid) from patients where 
                       year(createdon)=year(curdate()));
                if (last_id is null) then
                    set @yearly_id := last_id + 1;
                else
                    set @yearly_id := 1;
                end if;
                set new.yearlyid = yearly_id;
           end;
           end if;
       END;//

I want to increment the yearlyid column upon insertion of a Patient record. However, I am only getting 0 as YearlyId for every insertion. Why is that? YearlyId column if of type int.

JoelCrypto · Accepted Answer · 2022-06-03 19:12:16Z

1

You need to call the variable using @ as well. https://www.mysqltutorial.org/mysql-variables/

answered Jun 3, 2022 at 19:12

JoelCrypto

5623 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user3656651 Over a year ago

That does not help.

JoelCrypto Over a year ago

Then please update your code. Use déclare and variable name without @ or only use variables with @ and déclare them with set.

user3656651 Over a year ago

How do you declare a variable with set?

JoelCrypto Over a year ago

Check the link above.

user3656651 · Accepted Answer · 2022-06-08 07:05:47Z

The following code works, I initialized the variables and instead of using null in the if condition, used count.

CREATE TRIGGER generate_yearly_id BEFORE INSERT ON clinic.patients
       FOR EACH ROW
       BEGIN
           DECLARE last_id INTEGER default 0;
           DECLARE current_year DATE;
           DECLARE yearly_id INTEGER default 1;
           declare count INTEGER default 0;
           IF NEW.CustomYearlyId IS NULL THEN
           BEGIN
                #init last_id to some value before using
                SET @last_id = 0;
                SET @yearly_id = 0;
                #INSERT INTO DEBUG VALUES (null,"start trigger");
                set @count := (select count(*) from patients where
                        YEAR(CreatedOn)=YEAR(curdate()));
                IF @count > 0 THEN
                BEGIN
                    SET @last_id := (SELECT max(yearlyid) FROM patients WHERE 
                       YEAR(createdon)=YEAR(CURDATE()));
                    SET @yearly_id = @last_id + 1;
                    SET NEW.yearlyid := @yearly_id;
                    #INSERT INTO DEBUG VALUES (null, concat("in if lastid is not null ",@yearly_id));
                END;
                ELSE
                BEGIN
                    #INSERT INTO DEBUG VALUES (null, concat("in else ",@yearly_id));
                    #SET @yearly_id = 1;
                    SET NEW.yearlyid := 1;
                END;
                END IF;
                #Set NEW.yearlyid := 1;
           END;
           END IF;
       END;//
       delimiter ;

Collectives™ on Stack Overflow

MySQL Trigger not inserting expected value

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related