Problems with a trigger

Question

Problems with a trigger

Navigation

#1 by (1 votes)

2

Hi, I'm trying to make a trigger with a simple update.

delimiter //
CREATE TRIGGER 'reCalibrados' AFTER INSERT ON 'calibrationtbl'
FOR EACH ROW
    BEGIN
        UPDATE calibrationtbl SET state='1' WHERE idInteva=
        (SELECT idInteva FROM calibrationcertificatestbl WHERE 
        Month(nextCalDate)= Month(NOW()) AND Year(nextCalDate)= YEAR(NOW()));
    END;//
    delimiter ;

I get this error:

Unrecognized statement type (near end);

Can you help me?

mysql phpmyadmin

asked by Yami 17.02.2017 в 20:13

source

1 answer

Loop syntax when it has no results Send image to the server with MultipartEntityBuilder and HttpURLConnection

score 1 · Accepted Answer

In the subquery, a space is missing before the AND

WHERE...Month(NOW()) AND Year(nextCalDate)=YEAR(NOW()))

Likewise, I would use a delimiter to define the trigger

  DELIMITER $$

  CREATE TRIGGER...
     .....
  END */$$
  DELIMITER ;

In the documentation , give an example of how to create it using a block of transaction and requires using special delimiters so that you can distinguish between the end of the trigger block and the end of the DDL instruction (that is why it "fails" in the END)

mysql> delimiter //
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
    -> FOR EACH ROW
    -> BEGIN
    ->     IF NEW.amount < 0 THEN
    ->         SET NEW.amount = 0;
    ->     ELSEIF NEW.amount > 100 THEN
    ->         SET NEW.amount = 100;
    ->     END IF;
    -> END;//
mysql> delimiter ;