There is a table "vizits", in it there are fields date and weeks. Want to make a trigger? so that when added to the table, the weeks field will calculate the week number from the date field:

update vizits set weeks = weekofyear(date)

Tell me how to do this, never did. Tried on the manual, does not roll:

 CREATE TRIGGER `vizits`.`after_insert_date` AFTER INSERT ON `vizits` FOR EACH ROW BEGIN update vizits set weeks = weekofyear(date); END
  • 2
    Learning to use [Google] [1], the question is from the documentation! [1]: google.by/… - metazet
  • and what exactly does not roll? - draev
  • SQL Error (1064): You have an error in your SQL syntax; MySQL server at the line 4 - Malus

2 answers 2

CREATE TRIGGER insert_date before INSERT ON vizits FOR EACH ROW begin set new.weeks = weekofyear (new.date) end;

  • Does not want: SQL Error (1064): You have an error in your SQL syntax; If you’re the right line, you’ll find out what you’re trying to use. ”VERSION (): 5.1.37-1ubuntu5.5 - Malus
  • delimiter //; CREATE TRIGGER insert_date before INSERT ON vizits FOR EACH ROW begin set new.weeks = weekofyear (new.date) end; - mochalygin
  • It works, the trigger was added without any problems ... but now when you add it it scolds like this: / * SQL Error (1452): Cannot add or update a row for a foreign key constraint fails ( vizits . vizits , CONSTRAINT FK_vizits_doctor FOREIGN KEY ( doctor_id ) REFERENCES doctor ( id )) * / But it works at the same time) - Malus
  • Understood, everything is ok))) ATP is huge. ATP - Malus
  • mysql.ru/docs/mysql-man-5.0-en/introduction.html#constraints androschuk.blogspot.com/2011/05 / ... The error is not in the trigger. In theory, without a trigger it would be the same. - mochalygin

It is impossible to perform operations with the same table on which the trigger was activated. Try rewriting the trigger using "before", and changing not the entry in the table, but the data before inserting OLD.weeks = znachenie, so that the corrected value is inserted.

  • I do not understand how (can I try it?) And I also need weeks to be calculated from the date field - Malus
  • CREATE TRIGGER insert_date before INSERT ON vizits FOR EACH ROW begin set new.weeks = 100500 end; - mochalygin