以下是患者表的定義:
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
我在 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 ;
語句 SET @yearly_id = 1;
正常運作。但是如果我將其更改為 SET @yearly_id = @last_id;
,則觸發失敗。我在做這個作業時做錯了什麼? last_id有預設值,所以我需要初始化它嗎?
下面的程式碼可以完美運行。不同之處是 1. 變數在使用前已初始化。顯然預設值是不夠的。 1. 在「if」條件中使用了計數,而不是測試「is null」。