Help create a trigger, I do not understand what the error is ....

DELIMITER // CREATE TRIGGER trInsRaboty AFTER INSERT ON Raboty FOR EACH ROW BEGIN SET AUTOCOMMIT=0; START TRANSACTION; IF (select count(*) from Raboty where (IdRabot = new.IdRabot))<VidyRabot.KolvoSotrudnikov then (Insert raboty values(null,new.IdVidaRabot,new.IdSotrudnika,new.DataNach,(select Dni_NaVypolnenie from VidyRabot where idVidaRabot=new.IdVidaRabot)+new.DataNach);); Else ROLLBACK; EndIF; COMMIT; END; // DELIMITER ; COMMIT; END; // DELIMITER ;

Added from comment.

No, it looks like a line by line (through the console I work) an error like

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select IdSotrudnika from Raboty where (IdRabot = new.IdRabot))<VidyRabot.KolvoSo' at line 5

  • And it looks like that, in one line? (1) What a mistake (2) - alexlz
  • Moved to the question. - DrDeimos
  • What is your IF? if function syntax, and is used where IF STATEMENT should be - alexlz
  • And if so? swearing on Insert ... - DrDeimos
  • And what is new.IdRabot? And in general, the AFTER INSERT ON Raboty trigger, in which the INSERT raboty is executed ... - alexlz

1 answer 1

There are several errors: syntax errors associated with the placement of parentheses, and errors in the use of transaction operators. The fact is that triggers cannot be used for transactions, and in your case there is no need for this, since all operations are atomic and do not spoil the database, hence there is no place to roll back. After cleaning your trigger might look like this.

 DELIMITER // CREATE TRIGGER trInsRaboty AFTER INSERT ON Raboty FOR EACH ROW BEGIN IF (SELECT COUNT(*) FROM Raboty WHERE (IdRabot = NEW.IdRabot)) < VidyRabot.KolvoSotrudnikov THEN INSERT INTO Raboty VALUES ( NULL, NEW.IdVidaRabot, NEW.IdSotrudnika, NEW.DataNach, (SELECT Dni_NaVypolnenie FROM VidyRabot WHERE idVidaRabot = NEW.IdVidaRabot) + NEW.DataNach ); END IF; END//

Perhaps you wanted to cancel the insert transaction of the main record, but this definitely should not be done in the AFTER trigger that is triggered after the record is inserted. For these purposes, it is better to consider the BEFORE filter.