MySQL Trigger statement fails with variable assignment

Question

Following is the definition for Patients table:

Patients table Columns:
PatientId int AI PK 
FirstName longtext 
LastName longtext 
Email longtext 
NIC longtext 
Phone longtext 
Address longtext 
City longtext 
Country longtext 
DOB datetime(6) 
Gender longtext 
Reference longtext 
SerialNumberYear smallint 
SerialNumber int 
DoctorId int 
CreatedOn datetime(6) 
UpdatedOn datetime(6) 
CreatedBy longtext 
SMS_Allowed tinyint(1)
Email_Allowed tinyint(1) 
SpecialConcern1 longtext 
SpecialConcern2 longtext 
SpecialConcern3 longtext 
SpecialConcern4 longtext 
CustomYearlyId longtext 
YearlyId int

I have the following trigger in MySQL on this table:

CREATE TRIGGER generate_yearly_id BEFORE INSERT ON clinic.patients
       FOR EACH ROW
       BEGIN
           DECLARE last_id INTEGER default 10;
           DECLARE current_year DATE;
           DECLARE yearly_id INTEGER default 0;
           IF NEW.CustomYearlyId IS NULL THEN
           BEGIN
                INSERT INTO DEBUG VALUES (null,"start trigger");
                 #SET @last_id := (SELECT max(yearlyid) FROM patients WHERE 
                 #      YEAR(createdon)=YEAR(CURDATE()));
                IF last_id IS NOT NULL THEN
                BEGIN
                    SET @yearly_id = 1;
                    INSERT INTO DEBUG VALUES (null, concat("in if lastid is not null ",@yearly_id));
                END;
                ELSE
                BEGIN
                    #SET @yearly_id := 1;
                END;
                END IF;
                SET NEW.yearlyid := @yearly_id;
           END;
           END IF;
       END;//
       delimiter ;

The statement SET @yearly_id = 1; works fine. But if I change it to SET @yearly_id = @last_id;, the trigger fails. What am I doing wrong in making this assignment? last_id has a default value, so do I need to initialize it?

You are not setting last_id anywhere - please read https://stackoverflow.com/questions/11754781/how-to-declare-a-variable-in-mysql and is customyearlyid the same as yearlyid - please add table definition as text.and last_id has a default.. — P.Salmon, Jun 04 '22 at 11:22
Added table definition. last_id has a default value, do still need to initialize it? — user3656651, Jun 04 '22 at 11:43
IF last_id IS NOT NULL THEN can never be true...so the else seems pointless. but also :@yearly_id will always be set to 1. — P.Salmon, Jun 04 '22 at 11:45
It is true. When I have this statement ```SET @yearly_id = 1;```, I get insertion in the Debug table. So it has to be true. — user3656651, Jun 04 '22 at 11:49
Yes, for now it is never null, until I start using the commented out query, when it can be null. For now I am trying to resolve the variable assignment issue :) — user3656651, Jun 04 '22 at 12:01
You might be right about initialization. I set last_id to a value and then it works! — user3656651, Jun 04 '22 at 12:07
Like in your previous question, you don't understand that in MySQL, `last_id` and `@last_id` are not the same variable. — Bill Karwin, Jun 04 '22 at 15:37

score 0 · Answer 1 · answered Jun 08 '22 at 07:04

The following code works perfectly. Differences are 1. Variables have been initialized before being used. Apparently default value is not enough. 1. Count has been used in the "if" condition instead of testing for "is null".

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 ;

MySQL Trigger statement fails with variable assignment

1 Answers1