The first time I write a trigger, tell me what the problem may be.

There is the first table (table) , and in it the value quantity (qt) . Depending on the number, a field (num) should be created in the second table. The field value is performed as numbering, so it must take the last value and automatically write further.
Nap: if the last value (table2:num) was 2, and the quantity (table:num) 3, starting with 3 it should be written (3,4,5) (table2:num) .
The trigger was created, but the problem is that there is nothing to happen after insert : table
Tell me what I wrote wrong, and why it was created, but why does not it?

Here is the trigger that I wrote:

 DROP TRIGGER /*!50032 IF EXISTS */ `t_mat $$` CREATE TRIGGER `t_mat` AFTER INSERT ON `table` FOR EACH ROW BEGIN DECLARE i INT; DECLARE num2 INT; SET i=1; SET num2=(SELECT MAX(num) FROM table2); WHILE i<= new.qt DO SET num2=num2+1; INSERT INTO table2(num_mat, pole2,pole3) VALUES(num2,new.id_m,new.ps); SET i=i+1; END WHILE; END$$ DELIMITER ;
  • And then use the loop inside the norms norm trigger or not? - Zolushka
  • Nothing happens to you because you have not initialized the loop counter i - user218976
  • And the variable num2 is not defined. But the variable num is defined, but not used. - Akina
  • I changed the code like this, but now gives an error, with in the line SET num2=num2+1 - Zolushka
  • Thank you Anamnian and Akin for finding my mistakes. Now I will be happy to write a trigger, but I used to think that it was not for me. - Zolushka

1 answer 1

I managed.

DELIMITER $$

CREATE /*[DEFINER = { user | CURRENT_USER }]*/ TRIGGER t_mol AFTER INSERT ON table FOR EACH ROW BEGIN DECLARE i INT; DECLARE num2 INT; SET i=1; SET num2=(SELECT MAX(num_mat) FROM table2); WHILE i<=new.qt DO SET num2=num2+1; INSERT INTO table2(num_mat,pole2,pole3) VALUES (num2,new.id_m,new.ps); SET i=i+1; END WHILE; END$$ DELIMITER ; /*[DEFINER = { user | CURRENT_USER }]*/ TRIGGER t_mol AFTER INSERT ON table FOR EACH ROW BEGIN DECLARE i INT; DECLARE num2 INT; SET i=1; SET num2=(SELECT MAX(num_mat) FROM table2); WHILE i<=new.qt DO SET num2=num2+1; INSERT INTO table2(num_mat,pole2,pole3) VALUES (num2,new.id_m,new.ps); SET i=i+1; END WHILE; END$$ DELIMITER ;


Thank you all for not leaving without attention!