Pernyataan pencetus MySQL gagal pada penugasan pembolehubah

Question

Berikut adalah definisi jadual pesakit:

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

Saya mempunyai pencetus berikut pada jadual ini dalam MySQL:

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 ;

kenyataan SET @yearly_id = 1; 工作正常。但是如果我将其更改为 SET @yearly_id = @last_id; , pencetus gagal. Apakah salah saya semasa membuat tugasan ini? last_id mempunyai nilai lalai jadi adakah saya perlu memulakannya?

Answer 1

Kod di bawah berfungsi dengan sempurna. Perbezaannya ialah 1. Pembolehubah telah dimulakan sebelum digunakan. Nampaknya nilai lalai tidak mencukupi. 1. Digunakan kiraan dalam keadaan "jika" dan bukannya ujian "adalah batal".

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 ;