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?
![[Web front-end] Permulaan pantas Node.js](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
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 ;