Solve the problems encountered by phpMyadmin in creating triggers for MySQL data tables

The following tutorial column of phpmyadmin will introduce to you the problems encountered in using phpMyadmin to create triggers for MySQL data tables. I hope it will be helpful to friends in need!

Today I created a trigger for the table in the MySQL database. The desired effect is: after modifying the received field in table a, the registerStatus in table b must also be changed. Updated to 1, change registerIP to receive value.

1. First write the sql statement to create the trigger:

1) When inserting:

CREATE TRIGGER insertref BEFORE INSERT ON a
 FOR EACH ROW BEGIN 
UPDATE b SET registerStatus =1 WHERE NEW.id = id and NEW.received is not null and NEW.received !='';
update b set registerIP=NEW.received where NEW.id = id;
end

2) When deleting:

CREATE TRIGGER deleteref BEFORE DELETE ON a
 FOR EACH ROW begin
update b set registerStatus =0 WHERE OLD.id = id;
update  b set registerIP=NULL where OLD.id = id;
end

3) When updating:

CREATE TRIGGER updateref  BEFORE UPDATE ON a
 FOR EACH ROW BEGIN 
update b set registerIP=NEW.received where OLD.id=id;
end

Question 1: When

finishes writing the statement and executes it, an error is reported and cannot be executed. The reason is that when using phpMyAdmin to create a trigger, you need to

Enter // in the Delimiter at the bottom of the sql statement page.

Question 2: After setting up the trigger, during testing, data cannot be inserted into table a, and an error message is reported:

1558 - Column count of mysql.proc is wrong. Expected 20, found 16. Created with MySQL 50018, now running 50146. Please use mysql_upgrade to fix this error.

There are many posts on the Internet saying that it is because of previous upgrades. The database, but the data structure has not been upgraded.

Solution: Execute sudo mysql_upgrade -u root -p to prompt for a password. After entering the password, restart the database. Then you can insert data.

The above is the detailed content of Solve the problems encountered by phpMyadmin in creating triggers for MySQL data tables. For more information, please follow other related articles on the PHP Chinese website!