[FIXED] phpMyAdmin storing a random number in the 2nd table on the execution of trigger made on insert of first table

Issue

I have a table by the name of "Log_Data" which has two fields,

• log_date of "date" datatype
• log_information of "varchar" datatype with max size = 250

I have another table by the name of "Patient" which has many attributes, 2 of which are,

• patient_ID
• p_name

Now, I am trying to create a Trigger on "Insert" on "patient" table. The trigger is selected to be made on "insert" and "after" the execution of insert. The trigger definition is as follows,

insert into log_data
 values(NOW(), 'New patient with Id  = ' + new.patient_id + ' and name = ' + new.p_name + ' is added')

Please note that the first attribute of log_data is "log_date" and second attribute is "log_information"

After having done this, I made a new insertion in "patient" table, which was as follows,

INSERT INTO patient(patient_id, p_name, p_gender, p_address, p_phone_no, p_age, p_CNIC, p_isolation_id, p_quarantine_id, p_city, p_province, p_status) VALUES ('7','Asad Hussain','Male','Lasani Town House #5-B','03041502627','23','3310256209012','1',NULL,'Faisalabad','Punjab','ACTIVE')

Now, the command executes successfully... It also inserts into patient successfully, but it is not working for the log_data table. When I go to log_data table, it shows the time added correctly (i.e. log_date is fine) but for log_information, it adds some random number in it. I executed insert queries multiple times, and these are the values I got,

2020-06-14
20200614232014

2020-06-14
20200614232209

2020-06-14
8

2020-06-14
8

2020-06-14
8
2020-06-14
8
2020-06-14
9

So, I tried changing trigger to "Before" from "After" and it still showed the same error.

Then I tried changing its datatype from varchar to "Text" and then it started to insert only '8' in the log_information (these times, the patient id of the entry I was adding was '8') Then I tried to insert a patient record with patient id = 9, and then this time, it stored '9' in log_information (I had also changed datatype back to varchar(250) from text this time as well)

So the way I see it, now it is inserting the "patient_id" into the log_information however skipping the rest of the information... Also, it added some random number for first two inserts, which I still don't get why.

Can anyone guide me as to how to resolve this issue and how to store the actual string I am trying to store in the log_data table?

Also, is it possible to store an auto-increment log_id using trigger? That will store the id of every log as it is created (and the log_id will be auto incremented, meaning we will not have to give it to the insert in log_data while writing the trigger) Thank you!

Solution

Please you always CONCAT to combine Text

insert into log_data
 values(NOW(), CONCAT('New patient with Id  = ', NEW.patient_id ,' and name = ' , NEW.p_name , ' is added'))

Answered By - nbk