CODEWITHSUNDEEP
mysqltriggers
user3656651
user3656651

Reputation: 820

MySQL Trigger not inserting expected value

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.

Upvotes: 0

Views: 33

Answers (2)

user3656651
user3656651

Reputation: 820

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 ;

Upvotes: 0

JoelCrypto
JoelCrypto

Reputation: 562

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

Upvotes: 1

Related Questions